Calculating time past midnight.

[Guest]

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?

 

[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

 

[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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[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.