Adding Time Values

[danbl]

I have a report that adds time for as many as six people. This isssue is when the time goes over 24 hours in total it drops the hours. The field(s) is formated in "short time" format.

Can anyone help resolve this???

 

[Jon K]

=Int(Sum([TimeField])*24) & ":" & Format(Sum([TimeField]),"nn")

.

 

[danbl]

Hay Jon,

Thanks !!!!!!!!!

This seems to work.

Dan

 

[The_Doc_Man]

danbl - the function might keep on working but the odds are you will run into this again, so I'm going to give you the theory on what happened.

Date/Time fields are DERIVED as opposed to NATURAL quantities. DOUBLE is a natural format. Date/Time is a "cast" (alternate viewpoint) of a DOUBLE. It is a "timeline" of days and fractions thereof since the last day of 1899. So Jan 1 of 1900 is day 1. When you compute time fractions in Date/Time variables, the moment you exceed a total of 1, you have changed a date, in effect.

When adding/computing elapsed times, you should NEVER use a date/time field. Use a natural DOUBLE field instead. This might necessitate some gyrations with functions to get the accumulated time to display correctly, but the result will be worth the learning experience.

Hint: Once the time has been added up, split it into integer and fractional parts, then convert the integer to ... an integer number of days. Convert the fraction as a number of hours, minutes, and seconds.

Or just count the days and fractions thereof without regard to the time formatting functions at all.

 

[Jon K]

Originally posted by The_Doc_Man

When adding/computing elapsed times, you should NEVER use a date/time field.

There is no need to worry about storing elapsed times in Access' date/time fields.


I have used my expression on a table containing short times in 196,608 records in a date/time field, and as you can see in my report in the attached database, my expression returns the same total of 1545557 hours 20 minutes as storing individual hours and minutes in two separate columns and doing the necessary arithmetic on the sum total of the two columns.

Hence the two separate columns of Hour and Minute confirmed the correctness of the date/time field in my test.

Had there been any errors in the date/time field, summing more than a hundred and ninety thousand records would have been enough to make them appear in my test results.
.

 

[EMP]

Thanks for the simple expression for summing elapsed times in date/time fields.

I expanded it to include the SECONDS:
=Int(Sum([ElapsedTime])*24) & ":" & Format(Sum([ElapsedTime]),"nn:ss")

and it was accurate to the final second! Amazing!

^

 

[Jon K]

Originally Posted by EMP
Thanks for the simple expression for summing elapsed times in date/time fields.

I expanded it to include the SECONDS:
=Int(Sum([ElapsedTime])*24) & ":" & Format(Sum([ElapsedTime]),"nn:ss")

and it was accurate to the final second! Amazing!

Thanks for including the seconds in the expression.


The expression works because internally a date/time field:-
a) stores times as double precision numbers so that numeric calculations can be carried out on them, and
b) regards 30 December 1899 as 0 so that it can treat points in time (as if on a timeline) and periods of time (elapsed times) in the same way.

So it would be erroneous to think that a date/time field is good for only storing points in time.


For those who are interested, here's an expression for displaying total time in days:hours:minutes:seconds:-

=Int(Sum([TimeField])) & ":" & Format(Sum([TimeField]),"hh:nn:ss")
.