Time Problem

[Sparky Peterson]

I have the time I clock in and the time I clock out in two adjoining
cells. In the third cell I have, =b1-a1, to see how many minutes/and
or hours said person worked. It works great until I came upon this
entry:

11:55 PM 12:05 AM

The answer should be 0:10, but instead it reads:

#####################

Everything works fine except for this one situation. Any suggestions?

Thanks,

Sparky

 

[JE McGimpsey]

Since XL stores times as fractional days, 11:55 PM is stored as
0.996527778 and 12:05 AM is 0.003472222. You have to add 1 to the later
time if the period branches across midnight. One way is to use the fact
that a boolean value is interpreted as 1 in a math formula:

=B1 - A1 + (B1<A1)

a somewhat more obscure way is

=MOD(B1-A1,1)

 

[mzehr]

Hi Sparky,
you are creating a negative time value.
Try using this formula =IF(B1<A1,24-(A1-B1),B1-A1)
Note: This assumes that the maximum amount of time is 24
hours

HTH