# Solved Calculating time function in Excel

June 8, 2012 at 15:43:39
Specs: Windows 7
 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.

See More: Calculating time function in Excel

June 8, 2012 at 19:28:25
 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 tocheck for the date first.If your data looks like:``` E F G H I 17) 6/8/2012 7:00 6/9/2012 7:00 ```In cell I17 use the formula:=IF(G17>E17,H17+1-F17,H17-F17)*24Note that we check the DATE cells to see if weneed to add a day.Format cell I17 as General or Number.See how that works.MIKEhttp://www.skeptic.com/

#1
June 8, 2012 at 17:34:45
 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.

Report •

#2
June 8, 2012 at 18:40:32
 Try formatting your result cell as General, not HH:MM:SSMIKEhttp://www.skeptic.com/

Report •

#3
June 8, 2012 at 18:48:46
 I currently have it formatted as Number with 2 decimal places, which is how we calculate payroll, 'General' also returns a zero.

Report •

Related Solutions

#4
June 8, 2012 at 19:04:13
 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.

Report •

#5
June 8, 2012 at 19:06:17
 Start End HoursDate Time Date Time 6/8/12 7:00 6/9/12 7:00 24.006/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))

Report •

#6
June 8, 2012 at 19:07:07
 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 24 ```Using your formula: =IF(F17>H17,H17+1-F17,H17-F17)*24With 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:SSMIKEhttp://www.skeptic.com/

Report •

#7
June 8, 2012 at 19:20:05
 I am using seperate cells for date and time. E F G H I17) 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.

Report •

#8
June 8, 2012 at 19:28:25
 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 tocheck for the date first.If your data looks like:``` E F G H I 17) 6/8/2012 7:00 6/9/2012 7:00 ```In cell I17 use the formula:=IF(G17>E17,H17+1-F17,H17-F17)*24Note that we check the DATE cells to see if weneed to add a day.Format cell I17 as General or Number.See how that works.MIKEhttp://www.skeptic.com/