CODEWITHSUNDEEP
stringexceltext
Kevin Stirnichuk
Kevin Stirnichuk

Reputation: 3

How to convert a text string into hours

I have data in the format as d.hh.mm.ss and hh.mm.ss and I need to conver both into a time value of HH.MM.SS

In A1 the text string is 16.21:38:27 in A2 the text string is 04:08:45

My resulting cell should show B1 405:38:27 B2 04:08:45

I have tried using =LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,255)) but the results are wrong A1 is shown as 403:29:24 A2 is shown as 104:45:00

I would like one formula for both strings

Upvotes: 0

Views: 2968

Answers (3)

barry houdini
barry houdini

Reputation: 46341

As 4:08:45 is a valid time format and 16.21:38:27 is not this formula should work for you in both cases:

=IF(ISNUMBER(A1+0),A1+0,RIGHT(A1,8)+LEFT(A1,FIND(".",A1)-1))

format result cells as [h]:mm:ss

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149295

@TimWilliams hit the nail on the head. You have a typo. Change 16.21:38:27 to 16:21:38:27 and it will work fine. You can additionally wrap your formula to check the length. If it is more than 8 Chars then it means a date is added to it. See this example

Try this (You can use this for both)

=IF(LEN(A1)>8,LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,255)),TIMEVALUE(A1))

SNAPSHOT

enter image description here

EDIT

I just noticed that you have hardcoded 255 in your formula. You don't need to do that. This will also work.

=IF(LEN(A1)>8,LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,LEN(A1)-SEARCH(":",A1)+1)),TIMEVALUE(A1))

BTW, to make it foolproof you make also add the TRIM function to the above formula as well...

Upvotes: 0

richardtallent
richardtallent

Reputation: 35374

Here it is:

= IF(ISERROR(FIND(".",A1)),
    TIMEVALUE(A1), 
    VALUE(LEFT(A1,FIND(".",A1)-1)) + TIMEVALUE(MID(A1, FIND(".", A1)+1, 8)))

If a "." doesn't exist in the string, it simply uses TIMEVALUE() to parse the time. Otherwise, it parses the portion before and after the "." separately. The number of days forms the integer portion of the result, and the time is computed as a fractional day. This is then formatted using the standard formatting dialog into [h]:mm:ss format.

If you want B1 to store a string of the converted hours/minutes/seconds rather than a formatted number, wrap the whole of the above in TEXT(formula above, "[h]:mm:ss").

Upvotes: 0

Related Questions