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)*24

The 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

Report •


✔ Best Answer
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:00am

If 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:00	 

In 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

http://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:SS

MIKE

http://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 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))


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)*24
With Cell I17 formatted as General

Changing the cell format to Number with two decimal places also works.

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

MIKE

http://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 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.


Report •

#8
June 8, 2012 at 19:28:25
✔ 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:00am

If 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:00	 

In 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

http://www.skeptic.com/


Report •

#9
June 8, 2012 at 19:40:59
That was it!! Thank you so much!!!

Report •

Ask Question