help witha formula

[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

 

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

If you enter as 7:00 pm in c5 and 2:30 am in D5 then in E5 type
=IF(D5<C5,D5-C5+12,D5-C5)
your will get 7:30 am
format E5 as time-13:30 (this is in excel 2000)
(format menu-number-time-13:30)
you will get
7:30 meaning 7 hrs and 30 minutes




you can copy the down or up the column

 

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