# time convertion

Microsoft Excel 2003 (full product)
November 4, 2009 at 06:34:14
Specs: Windows XP, 2gb
 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

See More: time convertion

#1
November 4, 2009 at 06:48:21
 Try changing your cell format to: [H]:MM:SSYou will find it under:Task BarFormatCellsCustomMake sure you use the one with the braces [ ]MIKEhttp://www.skeptic.com/

Report •

#2
November 4, 2009 at 07:26:46
 Hi,Make sure that the time you enter is recognized by Excel as a time.Enter an after-noon time, say 13:00Using cell formatting:a. set the format, selecting from Time, a format with PMlook to see if the cell shows 1:00 PM or similarthen b. set the format to a time format without PMlook 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 A1enter 16:00 in cell A2In cell A3 enter this formula =A2-A1The 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/60and you will convert the minutes to fractions of an hour, i.e., 0.5 in this exampleHope this helps with your calculations.RegardsPS 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.0012 noon on 04 Nov is 400121.50Excel starts counting days from 01 January 1900

Report •

Related Solutions