adding hours past 24

[ReportSmith]

I have a few data files that are imported from another system (that I can't
access/modify) into an Access application I put together. The files contain
a few fields with data values in 'Short Time' format (ex: 0:45 or 8:30).

I know there is no easy way of adding time values past 24 hours (or is
there?). I'm forced into adding the values using something like:

[FieldX] & ":" &
if(([FieldX]-Int(FieldX])*60)<10,"0"&([FieldX]-Int([FieldX])*60,([FieldX]-Int(FieldX])*60))

where [FieldX] is the sum of time values. But what if the sum is > 24?

I've read numerous posts on dealing with time values, but keep going back to
my lengthy (almost incomprehensible) formulas (but they do work).

I was thinking of converting the time values to general numbers, doing the
math and then reconverting back to hh:mm. If so, what's the best way?

Thanks for any help anyone can provide.

 

[Wayne Morgan]

The problem is that the Date/Time functions deal with real dates and times.
Elapsed times, although frequently formatted to look like times, aren't. For
example, an elapsed time of 3:28 is three hours and 28 minutes, not 3:28 AM.

To add elapsed times, you'll have to break them apart then add them. You can
multiply the hours by 60 and add that to the minutes then add up the minutes
or you can add the hours and minutes separately. Either way, once you've
done the addition, you'll have to break the minutes up into hours and
minutes (or days, hours, minutes or whatever you prefer) and, if you used
the first method, add the hours to the sum of hours you came up with.

I find it easiest to convert everything to the smallest unit I want in the
output, add up that, then format back into the way I want it displayed (i.e.
the second method listed above). Built-in formats won't work without some
help. When you split things back up, it is frequently easier to format it
using concatenation:

Example:
lngHours & ":" & lngMinutes

This will break second up into Hours:Minutes:Seconds

Public Function HHMMSS(lngSeconds As Long) As String
Dim lngTest As Long
HHMMSS = lngSeconds \ 3600 & Format((lngSeconds Mod 3600) / 86400, ":nn:ss")
End Function

 

[ReportSmith]

Thank you Wayne.
I also came to the same conclusion about splitting the time and recombining
it. I actually also have a formula in the same report that takes the time
down to minutes and then brings it back as hh:mm. It just seemed like I was
missing something - guess not.

I'll keep plugging at the the report and try to have some sort of
consistency with the formulas - maybe have all of them go down to minutes and
then back to hh:mm - right now it's all over the place, with temp variables
in invisible text boxes and in the master query on which the report is built.

Again, thanks.

The problem is that the Date/Time functions deal with real dates and times.
Elapsed times, although frequently formatted to look like times, aren't. For
example, an elapsed time of 3:28 is three hours and 28 minutes, not 3:28 AM.

To add elapsed times, you'll have to break them apart then add them. You can
multiply the hours by 60 and add that to the minutes then add up the minutes
or you can add the hours and minutes separately. Either way, once you've
done the addition, you'll have to break the minutes up into hours and
minutes (or days, hours, minutes or whatever you prefer) and, if you used
the first method, add the hours to the sum of hours you came up with.

I find it easiest to convert everything to the smallest unit I want in the
output, add up that, then format back into the way I want it displayed (i.e.
the second method listed above). Built-in formats won't work without some
help. When you split things back up, it is frequently easier to format it
using concatenation:

Example:
lngHours & ":" & lngMinutes

This will break second up into Hours:Minutes:Seconds

Public Function HHMMSS(lngSeconds As Long) As String
Dim lngTest As Long
HHMMSS = lngSeconds \ 3600 & Format((lngSeconds Mod 3600) / 86400, ":nn:ss")
End Function

--
Wayne Morgan
MS Access MVP

I have a few data files that are imported from another system (that I can't
access/modify) into an Access application I put together. The files
contain
a few fields with data values in 'Short Time' format (ex: 0:45 or 8:30).

I know there is no easy way of adding time values past 24 hours (or is
there?). I'm forced into adding the values using something like:

[FieldX] & ":" &
if(([FieldX]-Int(FieldX])*60)<10,"0"&([FieldX]-Int([FieldX])*60,([FieldX]-Int(FieldX])*60))

where [FieldX] is the sum of time values. But what if the sum is > 24?

I've read numerous posts on dealing with time values, but keep going back
to
my lengthy (almost incomprehensible) formulas (but they do work).

I was thinking of converting the time values to general numbers, doing the
math and then reconverting back to hh:mm. If so, what's the best way?

Thanks for any help anyone can provide.