Subtract Time over and past Midnight

[Guest]

I have an attendance tracker Excel file that works fine but I want to put it
into Access. The problem I have is Access cannot do time math as Excel
can...or at least I am missing something

i.e. Sally is sick she needs to leave early at 10pm but her shift ends at
2am. To calculate the math of missed ours properly I type 22:00 at time left
and 26:00 as scheduled time off. The 26:00 automatically calculates to 2:00
then another cell states 4:00 for hours missed. . If you code the cells as
date and time (just to look because I don't enter dates). Time left at 22:00
is 1/1/1900 22:00 and time left at 26:00 is 1/2/1900 2:00

So in turn is there a way to type 26:00 or so forth in Access and have it
reformat back to the correct time? I assume there might be a VB code to do
that but my VB code knowledge is nil. (Commodore 64 Basic? lots of knowlege
back in the day)

I apologize for the long drawn out explanation...just wanted to make sure I
was understood correctly!

Andy

 

[Allen Browne]

The problem disappears if you have both the date and time in one field.

For example, if Sally leaves 10pm 8/1/2006, when her shift finishes 2am
8/2/2006, the number of minutes she was missing is given by:
DateDiff("n", #8/1/2006 22:00:00#", #9/1/2006 2:00:00#)

For details on how to format the results, see:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

If you cannot include both the date and time, and you are certain there will
be how shifts that are more than 24 hours long, you could create an
expression with IIf() to add 1 day if the end time is less than the start
time.

 

[Guest]

Allen, you truly are an Access star. I should have know to do an IIF
statement.
I ended up doing the statement of:

=IIf([Shift End]<[Time Left],[Shift End]+1,[Shift End]+0) and it worked great.

Thanks again Allen