time convertion

Microsoft Excel 2003 (full product)
November 4, 2009 at 06:34:14
Specs: Windows XP, 2gb

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

See More: time convertion

Report •

November 4, 2009 at 06:48:21
Try changing your cell format to:


You will find it under:

Task Bar

Make sure you use the one with the braces [ ]



Report •

November 4, 2009 at 07:26:46

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
b. set the format to a time format without PM
look to see if the cell shows 13:00 or similar

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

If 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 30

In A6 enter =A5/60
and you will convert the minutes to fractions of an hour, i.e., 0.5 in this example

Hope this helps with your calculations.

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

Report •

Related Solutions

Ask Question