help witha formula

P

Peter

Hi,
I need a little help with a formula, I manage a night crew and need to total
their hours when they start at night and finish in the mornings.
In other words a worker starts at 7.00 pm or 11pm and might finish at
different times say at 2:20 am, or maybe 4 or 5 in the morning. Can anyone
help?
Thank you
Lori
 
B

Biff

Hi!

A1 = 11:00 PM
B1 = 7:00 AM

If you want the result displayed as 8:00

Format the cell as [h]:mm and use this formula:

=B1-A1+(A1>B1)

If you want the result displayed as the decimal value 8

Format the cell as GENERAL (which is usually the default) and use this
formula:

=(B1-A1+(A1>B1))*24

Biff
 
R

Roger Govier

Hi Peter

Another alternative to those solutions you have already received, would
be with start time in A1 and Finish time in B1
=MOD(B1-A1,1)
This will cope with crossing the 24 hour time frame.
If you are summing the results, format the cell as [h}:mm as already
pointed out to sum values and allow for totals greater than 24.
 
Top