Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 37
  • Last Modified:

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
0
agwalsh
Asked:
agwalsh
  • 2
  • 2
1 Solution
 
ProfessorJimJamCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now