Calculating time past midnight.

M

mailrail

I'm trying to write a formula that will calculate my employees' total time
worked. A problem arises when I have people scheduled to work past midnight.
If, say, someone works from 9 pm until 3 am, how do I get Excel to recognize
the change in dates and properly figure the time?
 
P

Peo Sjoblom

Start time in A1, end time in B1

=B1-A1+(B1<A1)

(format result as time)

or

=MOD(B1-A1,1)


will return 6:00 with 09:00 PM in A1 and 03:00 AM in B1
 
S

Sandy Mann

Assuming that your times are XL times like 21:00 and 3:00 and Start time in
B2, End time in C2 then try:

=MOD(C2-B2,1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
D

Dave Peterson

You may want to include the date and time in the cell. Then you can do just
regular arithmetic to get the hours.
 
Top