Forum Discussion
Solved
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 ...
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