Weird problem with time calculations. Sum won't work. Plus will.

In the attached file, if I try to add up the times using Autosum (A36) I get 0. If I add them up using +, (B2), I can add them up...anyone got any ideas as to what this might be?
odd_time_calculations.xlsx
agwalshAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
it is the formatting issue.  the reason it works with + is that any math operation converts the text like value back to numeric and the SUM do not work becuase it ignore the text hence it does not work.

if you select the cells and click into text 2 column and select delimiter and then click finish it will fix the issue and SUM will work.

to confirm that these cells are stored as text, you can put this formula in another cell =ISTEXT(A5)  and it will result true.

if you do not want them converted then use this formula =SUMPRODUCT(--(A5:A9))  that double negatives at the begining will forcefully convert the values in that range inside formula as numbers and then sumproduct will add them.
0
 
agwalshAuthor Commented:
Well, blow me down with a feather. I checked and the text to columns worked beautifully. Thank you.
0
 
agwalshAuthor Commented:
A superb answer to an annoying question...thank you :-)
0
 
ProfessorJimJamCommented:
you are welcome agwalsh.
0
All Courses

From novice to tech pro — start learning today.