Full Version: Calculating Elapsed Time over two days without date
mattcanty
I have daily timesheet records which, very rarely, go past 12 midnight. How do I calculate the elapsed time between, for example, 14:00pm to 03:00am. I do not want to use two dates as it would mean too much data input for a rare occurence. In the query I just have an 'Expression' field which subtracts the Start Time from the End Time. he result is given as a portion of 1 but is a minus figure if it goes to the next day. Does this make sense to anyone???
Alan_G
Hi
Welcome to UA
It makes perfect sense and has been discussed many times. Have a look here for a full explanation and the solution to your problem..........
HTH
Alan
Girn13
First, welcome.

You've got a bigger problem then positive or negative. 1400 to 0300 is 13 hrs and you can't get the right answer with simple math. What you've to do is....
A) if the end time is greater than the start time - straight subtraction
B) if the end time is less than the start time then accumulate the difference between the start time and 2400 and add it to the end time.
If course this all fails if they work more than 24 hrs. Dates are the only way to capture that event.
HTH
Rick
mattcanty
I have been able to calculate and display the correct elapsed time for each record. My problem now is that the overall total is being calculated using the 'SUM' of start times from end times which gives me the same problem but in the total field only.
I am fairly new to Access so I am not sure how to accumulate the individual fields to total level.