# 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!

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.`

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

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?

5. ## 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?
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?

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

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!

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"

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")

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

Thank you everyone!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1