Forum Discussion
time data changing when using the Time formatting options
I am NEW to Excel and have a problem with Time values. I have the following formula in a block (=SUM(F9+I9+L9+O9+R9+U9+X9)) which total times from the columns. The columns contain the elapsed time from clocking in and clocking out and the h:mm format works fine in the columns. Now, i have to total up the time spent working for 7 days for each employee. Using the format of h:mm:ss, all is good. 40:00:00 is displayed. when the format is changed to h:mm, the displayed value is changed to 16. Any format other than h:mm:ss gives the same results. I wish to use h:mm but not able to. What do I need to do to make the h:mm work. Web based research has not been of any help so far. Thanks for any help and or comments.
Use the custom format [hh]:mm:s
The square brackets around hh tell Excel to display a duration, not clock time.
Why? 40 hours equals 1 day plus 16 hours. Formatted a time that becomes the numerical value of 1.66666667 (i.e. 1 day + ⅔ of a day. Format this to TIME and the formula bar will display 01/01/1900 16:00:00 and the cell will display 16:00.
Use the custom format [hh]:mm:s
The square brackets around hh tell Excel to display a duration, not clock time.
Why? 40 hours equals 1 day plus 16 hours. Formatted a time that becomes the numerical value of 1.66666667 (i.e. 1 day + ⅔ of a day. Format this to TIME and the formula bar will display 01/01/1900 16:00:00 and the cell will display 16:00.
Riny_van_Eekelen Good morning! Thank you for the clear explanation of my situation. Your suggestion was spot on and worked properly. Additionally, i discovered that the file I inherited was constructed using Excel Ver 1.0 i'm guessing as several other issues were evident as i worked with it. I constructed a new file to mimic the original and no problems have been encountered. Thanks, again.
John
