# Excel convert Text time formatted 10h 58m to time so I can sum together

1. ## Excel convert Text time formatted 10h 58m to time so I can sum together

Hi,

My first post here!! I have an output from a time logging utility that outputs time as Text format:-

598h 56m
524h 58m
589h 34m
486h 28m

I'd like to sum these together but i'm afraid my Excel skills are not too good, I wondered if you could help me sort the information so I can SUM these together.

Thanks!  Register To Reply

2. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Hi, and welcome to the forum

One way...

Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

If Times are in A1 to A4 cells

try

=SUMPRODUCT(--(LEFT(A1:A4,FIND("h",A1:A4)-1)))+(SUMPRODUCT(--(MID(A1:A4,FIND(" ",A1:A4)+1,2))))/60  Register To Reply

4. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Hi Richard,

Thanks for the help, although I get this result?

524h 58m = 524.9666667

Seems a little out to me, any ideas?  Register To Reply

5. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together Originally Posted by slinka Hi Richard,

Thanks for the help, although I get this result?

524h 58m = 524.9666667

Seems a little out to me, any ideas?
Hi,

Why? 58 minutes is .966667 of an hour hence total hours IS 524.9666667.
What result do you think you should expect that will allow you to manipulate numbers arithmetically?  Register To Reply

6. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

...just another thought. Did you copy the formula to B1:B4 and then use an
Formula:  `Please Login or Register  to view this content.`
in say B5  Register To Reply

7. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Hi Richard,

Sorry I forgot to mention that Ideally i'd like to add them together the output as Hours and Minutes

10H 10m
11H 11m

= 21h 11m

Thanks!  Register To Reply

8. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Summing the results based on Richard's formula (individual results in B1 onwards) :

in B5

=INT(SUM(B1:B4))&"H "&INT(MOD(SUM(B1:B4),1)*60)&"M"  Register To Reply

9. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Without helper cells, Formatting A6 as [h]:mm
In A6
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(A1:A4,"h ",":"),"m","")))
If you want it as text
=TEXT(SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(A1:A4,"h ",":"),"m",""))),"[h]\h m\m")  Register To Reply

10. ## Re: Excel convert Text time formatted 10h 58m to time so I can sum together

Thank you everyone!!!     Register To Reply