I am creating a time sheet in Excel for a 24 hour shift schedule. Example: Start time is on Day 1 at 07:00 hours, End Time is on Day 2 at 07:00 hours. I have used this formula to get the hours worked,

=IF(F17>H17,H17+1-F17,H17-F17)*24The problem is the target cell shows 0.00 because it's 24 hours. I would like it to read 24.00.

If the times change then the cell reads properly; (07:00 to 06:59 = 23.98 hours)Any help would be great.

✔ Best Answer

If you are using separate Date & Time cells I would advise against it. Combine both Date & Time in one cell, it will make your life a lot easier.

What your doing now is subtracting 7:00am from 7:00am which of course

equals zero.

That's because the formula does not know that one 7:00am

is on a different DATE then the other 7:00amIf you must keep the date and time seperate, then modify your formula to

check for the date first.If your data looks like:

E F G H I 17) 6/8/2012 7:00 6/9/2012 7:00In cell I17 use the formula:

=IF(G17>E17,H17+1-F17,H17-F17)*24

Note that we check the DATE cells to see if we

need to add a day.Format cell I17 as General or Number.

See how that works.

MIKE

You could wrap another IF around your formula to check for a 0: =IF(IF(F17 etc.) = 0, 24, IF(F17 etc.))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I currently have it formatted as Number with 2 decimal places, which is how we calculate payroll, 'General' also returns a zero.

Thank You for the help!! I tried adding the IF around the formula which changed the zero to a 24.00, but when the time wasn't 24 hours, it went FALSE.

Start End Hours

Date Time Date Time

6/8/12 7:00 6/9/12 7:00 24.00

6/8/12 7:00 6/9/12 6:00 FALSE=IF(IF(F17>H17,H17+1-F17,H17-F17) = 0, 24, IF(F17>H17,H17=1-F17,H17-F17))

What does your data look like? Are you using a Date & Time or just Time.

I have it set up as:

F G H I 17) 6/7/2012 7:00 6/8/2012 7:00 24Using your formula: =IF(F17>H17,H17+1-F17,H17-F17)*24

With Cell I17 formatted as GeneralChanging the cell format to Number with two decimal places also works.

It only shows 00:00 if I format as H:MM:SS

MIKE

I am using seperate cells for date and time.

E F G H I

17) 6/8/12 7:00 6/9/12 7:00 ?I have tried formatting I17 to both Number and General, both give Zero. I must have something wrong.

If I change H17 to 6:00 it reads 23.00 or 23 depending on Number or General.

If you are using separate Date & Time cells I would advise against it. Combine both Date & Time in one cell, it will make your life a lot easier.

What your doing now is subtracting 7:00am from 7:00am which of course

equals zero.

That's because the formula does not know that one 7:00am

is on a different DATE then the other 7:00amIf you must keep the date and time seperate, then modify your formula to

check for the date first.If your data looks like:

E F G H I 17) 6/8/2012 7:00 6/9/2012 7:00In cell I17 use the formula:

=IF(G17>E17,H17+1-F17,H17-F17)*24

Note that we check the DATE cells to see if we

need to add a day.Format cell I17 as General or Number.

See how that works.

MIKE

That was it!! Thank you so much!!!

Ask Your Question

Weekly Poll