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

agwalsh
agwalsh used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
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.

Author

Commented:
Well, blow me down with a feather. I checked and the text to columns worked beautifully. Thank you.

Author

Commented:
A superb answer to an annoying question...thank you :-)
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome agwalsh.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial