time data changing when using the Time formatting options

[retiredoldguy101]

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. 

[#2 · Accepted solution]

@retiredoldguy101 

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.

[#3]

@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