Microsoft Excel 2003 (full product)

Hi, I have time in as 10am and time out as 7.30pm. Our work schedule is 8 and 1/2 hours. When I calculate the difference of time it shows 1.30hrs, but when I total the difference, one hour is calculated for 100 minutes instead of 60minuts. How can I calculate it in 60minutes format?

eg: 4 extra 30 minutes to be 2 hours but excel shows me 1hr 20 minutes. Pls help me with the formula to do it right

Try changing your cell format to: [H]:MM:SS

You will find it under:

Task Bar

Format

Cells

CustomMake sure you use the one with the braces [ ]

MIKE

Hi, Make sure that the time you enter is recognized by Excel as a time.

Enter an after-noon time, say 13:00

Using cell formatting:

a. set the format, selecting from Time, a format with PM

look to see if the cell shows 1:00 PM or similar

then

b. set the format to a time format without PM

look to see if the cell shows 13:00 or similarIf this works, then the times have been recognized by Excel as times.

Excel holds times in the decimal part of a number

(1 is the equivalent of a full 24 hour day).

0.5 is 12 noon i.e., half-way through the day.To get the difference just subtract the earlier cell from the later cell.

Enter 07:30 in cell A1

enter 16:00 in cell A2

In cell A3 enter this formula =A2-A1

The value in A3 is displayed as 08:30 if the cell is formatted as hh:mmIf you format A3 as a number with 4 decimal places it will show 0.3542 i.e. just over a third of a day.

To get hours and minutes out of A3,

in cell A4 put this formula =HOUR(A3)

in cell A5 put this formula =MINUTE(A3)

and you will get 8 and 30In A6 enter =A5/60

and you will convert the minutes to fractions of an hour, i.e., 0.5 in this exampleHope this helps with your calculations.

Regards

PS Excel stores dates in the same numbers as times, its just that the date is in the integer or whole number part of the number.

04-Nov-2009 is 400121.00, 05 Nov will be 400122.00

12 noon on 04 Nov is 400121.50

Excel starts counting days from 01 January 1900

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History