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

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History