0

I know this is a question that has been asked before, but no matter what I check or try, I cannot seem to get excel to work with me.

So I have a large spreadsheet filled with phone times. For some reason the program being used to collect those times uses a different format for the times, and it is preventing me from summing everything up.

Here's a quick example of just the times:

01:00:01
01:00:00
:59:54
:59:53

And a lot more like that. Due to the ':' at the beginning of the short times, I can't seem to get it to sum up with the longer times.

I attempted a few things suggested by others with this issue, but no luck. I tried changing the format to number, to time, using custom format [h]:mm:ss, /:mm:ss, but I always get either 0 or 12:00:00 AM as answers.

Is there any way to get excel to sum up all those times together accurately?

Improve this question

3 Answers 3

Reset to default
1

Here is how to sum your time values without creating any extra columns:

=SUM(IFERROR(VALUE(A1:A99),VALUE(0&A1:A99)))

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

=SUM(IFERROR(1*TEXT(A1:A4, "hh:mm:ss"),1*("0"&A1:A4))) Tried that, and it gave me the output of 01:00:01 (the value from A1)
@user5504808 Yes, but why complicate things?
You're right, I didn't use your exact formula, so I tried again. =SUM(IFERROR(VALUE(A1:A4),VALUE(0 & A1:A4))) I received the #VALUE! error.
Did you confirm it with Control-Shift-Enter?
Oops, must have missed one of those when I did it the first time. That worked! Just have to convert the output to custom [h]:mm:ss and it counts correctly. Thanks so much.
1

Slightly shorter: 

=IFERROR(1*TEXT(A1,"hh:mm:ss"),1*("0"&A1))
Improve this answer

1 Comment

I tried this one. =IFERROR(1*TEXT(A1:A4, "hh:mm:ss"),1*("0"&A1:A4)) Unfortunately it just gave me the output of 01:00:00 (the same value as A2 for some reason)
1

Where A1 contains your time value as a text string, this formula will convert it to an Excel readable time value that you can perform math on (SUM, etc):

=TIMEVALUE(IF(LEFT(A1,1)=":","0"&A1,TEXT(A1,"[H]:mm:SS")))

The default output will be a decimal, where a whole 1 represents 24:00:00, 0.5 = 12:00:00, 0.25 = 06:00:00, and so on. This in turn can be formatted back to a timestamp using Excel's built-in formatting options.

I would add a helper column to store the converted values and them SUM the ones you want. You can format the result as [H]:mm:SS to see the total time (this format is allowed to exceed 24 hours).

It would be possible to use array formulas as well to nest the TIMEVALUE conversion formula inside a SUM formula, but it's more complicated so I'll need some time to think about it...

EDIT - See @ExcelHero's answer for the better array option.

Improve this answer

3 Comments

Your formula is difficult to use with mine, but I tried to insert into it: =TIMEVALUE(IF(LEFT(A1:A4,1)=":","0"&A1:A4,TEXT(A1:A4,"[H]:mm:SS"))) This gave me the #VALUE! error.
Yes, if you're using arrays instead of single cells then it would need to be entered as an array formula (Ctrl + Shift + Enter) like this: =SUMPRODUCT(TIMEVALUE(IF(LEFT(A1:A4,1)=":","0"&A1:A4,TEXT(A1:A4,"[H]:mm:SS"))))
TIMEVALUE() was the key for me when using formula for the time values being summed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.