date/time

[Guest]

Hi I have a table which records DayIn, LunchOut, LunchIn, LunchOut,
using the data above in a query I can calculate Net working Hours i.e.
Sum: [LunchOut]-[DayIn]+[DayOut]-[LunchIn]
All fine until here.

Now when I want to show Net weekly or Monthly hours I get very funny figure
even after using hh:nn format.

Can someone help?

 

[Allen Browne]

The JET date/time type is a floating point number where the integer part
represents the day, and the fraction the time of day. Day zero is Dec 30
1899, so if you sum time values beyond 24 hours, Access reports the sum of
the times as Dec 31 or a later date. If you explicitly format hh:nn, you are
suppressing the date part, and only showing the number of hours.

The best solution is to use DateDiff() to calculate the number of minutes,
i.e.:
Sum: DateDiff("n", [DayIn], [LunchOut]) + DateDiff("n", [LunchIn], [DayOut])

You can then perform whatever calculations you want on the result.

More info:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html