help witha formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 09 Jan 2006 06:09 GMT

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

Reply to this Message

Biff - 09 Jan 2006 06:20 GMT

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

> Hi,
> I need a little help with a formula, I manage a night crew and need to
[quoted text clipped - 4 lines]
> Thank you
> Lori

Reply to this Message

R.VENKATARAMAN - 09 Jan 2006 07:11 GMT

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

> Hi,
> I need a little help with a formula, I manage a night crew and need to total
[quoted text clipped - 4 lines]
> Thank you
> Lori

Reply to this Message

Roger Govier - 09 Jan 2006 09:42 GMT

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.

Signature

Regards

Roger Govier

> Hi,
> I need a little help with a formula, I manage a night crew and need to
[quoted text clipped - 4 lines]
> Thank you
> Lori

Reply to this Message