Total Time Calculation

**acastell** · 06-29-2011, 09:53 PM

I am trying to calculate total time worked from 5 PM to 7 AM with no breaks. I have already tried the following formula but it does not fit the need over midnight.
=IF((D57-C57)*24)>8,8,((D57-C57)*24))

Column D start time Column C end time
Please Help

**jeffreybrown** · 06-29-2011, 10:36 PM

Hello & Welcome to the Board,

Could this be what you need...

=IF(MOD(E57-D57,1)>TIME(8,0,0),TIME(8,0,0),MOD(E57-D57,1))

You can format as [h]:mm

Originally Posted by acastell

I trying to calculate total time worked from 5 PM to 7 AM with no breaks. I have already tried the following formula but it does not fit the need over midnight.

With the formula you had, why are you returning 8 hours with anything greater than 8 hours but you say you want to calculate time from 5 PM to 7 AM?

**acastell** · 06-30-2011, 09:50 AM

Originally Posted by jeffreybrown

Hello & Welcome to the Board,

Could this be what you need...

=IF(MOD(E57-D57,1)>TIME(8,0,0),TIME(8,0,0),MOD(E57-D57,1))

You can format as [h]:mm

With the formula you had, why are you returning 8 hours with anything greater than 8 hours but you say you want to calculate time from 5 PM to 7 AM?

Unless I'm doing it wrong, no this is not it. If E57 is time punched out and D57 is time punched in, than this formula returns .3333333 or formatted in [h]:mm it returns 8:00. Sorry I don't think this is it. It should total 14.00. Thanks Jeff

**jeffreybrown** · 06-30-2011, 09:56 AM

=mod(e57-d57,1)

**acastell** · 06-30-2011, 05:54 PM

Originally Posted by jeffreybrown

=mod(e57-d57,1)

Jeff thanks but this almost gets me there. I have attached a sample spreadsheet. I have some people working am to pm and some working pm to am. The formula you sent works for pm to am and not for am to pm. If you could look one more time I would really appreciate it
AJ

**daddylonglegs** · 06-30-2011, 06:09 PM

Jeff's formula works for me if you want to see result in time format like 14:00, e.g. in D3 use this formula

=MOD(C3-B3,1)

format as h:mm and copy down. Make sure you format the total cell (D10) as custom [h]:mm (note the square brackets) otherwise you won't get the right total

If you just want to see 14 then multiply by 24, i.e. use this formula instead

=MOD(C3-B3,1)*24

format cells as general