SUM of time in column

%3CLINGO-SUB%20id%3D%22lingo-sub-2065641%22%20slang%3D%22en-US%22%3ESUM%20of%20time%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065641%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20sum%20a%20column%20of%20time.%20When%20I%20put%20in%20%3DSUM(G8%3AG120)%20it%20give%20me%20an%20output%20of%2000%3A00%3A00%20despite%20having%20the%20cell%20formatted%20as%20%5Bh%5D%3Amm%3Ass.%20But%20when%20I%20do%20%3DSUM(G8%2BG9%2BG10)%20etc%2C%20the%20cell%20output%20works%20perfectly.%20What%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2065641%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065859%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20of%20time%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F930939%22%20target%3D%22_blank%22%3E%40Katie345%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETime%20in%20Excel%20is%20actually%20number%20formatted%20in%20human%20friendly%20form.%20Most%20probably%20you%20have%20not%20such%20numbers%20but%20texts%20which%20present%20time.%20You%20can%20convert%20one%20to%20another%20just%20by%20applying%20time%20format.%20First%20you%20need%20to%20re-enter%20such%20texts%20as%20number%20(aka%20actual%20time)%2C%20after%20to%20apply%20desired%20format.%20One%20of%20the%20way%20to%20convert%20is%20to%20use%20such%20text%20value%20in%20arithmetic%20operation%2C%20thus%20you%20have%20correct%20result%20using%20%2B.%3C%2FP%3E%0A%3CP%3ESimple%20sample%3A%20we%20have%203%20numbers%20entered%20as%20texts%20and%20apply%20to%20them%20formulas%20similar%20to%20yours%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20244px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246808i9555E552FB855AA4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20note%2C%20SUM()%20ignores%20all%20texts%2C%20thus%20it%20returns%20zero%20in%20first%20case.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I need to sum a column of time. When I put in =SUM(G8:G120) it give me an output of 00:00:00 despite having the cell formatted as [h]:mm:ss. But when I do =SUM(G8+G9+G10) etc, the cell output works perfectly. What am I doing wrong?

1 Reply

@Katie345 

Time in Excel is actually number formatted in human friendly form. Most probably you have not such numbers but texts which present time. You can convert one to another just by applying time format. First you need to re-enter such texts as number (aka actual time), after to apply desired format. One of the way to convert is to use such text value in arithmetic operation, thus you have correct result using +.

Simple sample: we have 3 numbers entered as texts and apply to them formulas similar to yours:

image.png

Please note, SUM() ignores all texts, thus it returns zero in first case.