CODEWITHSUNDEEP
excelexcel-formula
Adam Robertson
Adam Robertson

Reputation: 13

Data Re-formatting from d.hh:mm:ss to hh:mm:ss

I have a data set with a strange format as shown below which i need to convert to HH:MM:SS format

Sample Data:

Format is Day.hour:minute:second - it is a string

01.15:00:15 - Expect Output 39:00:15

00.05:01:00 - Expect Output 05:01:00

02.00:10:15 - Expect Output 48:10:15

I have tried this code below,

=24*LEFT(A2,FIND(":",A2)-1)+MID(A2,FIND(":",A2)+1,LEN(A2)-2-LEN(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,":","@",2))))-LEN(LEFT(A2,FIND(":",A2)-1)))&":"&TEXT(RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,":","@",2))),"00")

This is the Output i get for example 1 after i manually change the "." between day's and hours to a ":"

Output - 39:00

Upvotes: 1

Views: 392

Answers (1)

JvdV
JvdV

Reputation: 75850

You overcomplicated the matter. Try the following:

enter image description here

Formula in B1:

=TEXT(--LEFT(A1,2)+TIMEVALUE(MID(A1,4,8)),"[HH]:MM:SS")

Though, these are yet again text values looking like timevalues. So you could also leave out the TEXT() function and custom format your cell to keep the numeric value.

Upvotes: 1

Related Questions