Solved

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

Posted on 2016-09-28
4
27 Views
Last Modified: 2016-09-28
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
Comment
Question by:agwalsh
  • 2
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41819524
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
 

Author Comment

by:agwalsh
ID: 41819559
Well, blow me down with a feather. I checked and the text to columns worked beautifully. Thank you.
0
 

Author Closing Comment

by:agwalsh
ID: 41819560
A superb answer to an annoying question...thank you :-)
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41819561
you are welcome agwalsh.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now