Help with time over two days please?

[Derek Peters]

HI thanks for looking,


I have a sheet that I enter a start time into 12:30 PM (Cell formatted to
time (Cell A1)) and a finish time of 2:30 AM (Cell formatted to time (Cell
B1)) so I have worked a total of 14 hours.

Can you please give me a formula that will work over the two days and also
work if I start at 12:30 AM and finish at 10:30 PM on the same day IE 8
hours worked.

Many thanks for your help
Derek

 

[JE McGimpsey]

One way:

Assuming the total time period is less than 24 hors, add 1 if the
"later" time is less than the "earlier" time (since XL stores times as
fractional days, 1 is equivalent to 24 hours). Use XL's implicit
conversion of boolean TRUE/FALSE values to 1/0 respectively:

= B1 - A1 + (B1<A1)

A little more obscure, but shorter:

= MOD(B1-A1,1)

 

[Harald Staff]

Hi Derek

If I understand you correct:

=B1-A1+(B1<A1)

The thing in the parentheses adds 1 (=24 hours) if end time B1 is smaller
than start time A1.

HTH. Best wishes Harald

 

[Derek Peters]

Many thanks that's sorted

Derek