0

I have data that is in the format of 'd hr m s' and want to change it to [h]:mm. Where the h is the d*24+hr.

For example, this is the data that I currently have:

enter image description here

What can I do to format the cell, or what formula can I use to change the cell data to the format that I want?

Cheers

1
  • @Akina i have no experience using VBA at the moment, some other users posted formula's that work well, thanks Oct 17, 2019 at 21:46

3 Answers 3

1

Creates a time format that Excel will recognize and adds the days

=IFERROR(LEFT(A1,FIND("d",A1)-1),0)+(IF(ISNUMBER(FIND("hr",A1)),"","00:")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,IFERROR(FIND("d",A1),-1)+2,LEN(A1)),"hr ",":"),"m ",":"),"s",""))

enter image description here

1
  • Thanks a lot mate, this was the solution i needed, cheers Oct 17, 2019 at 21:46
0

You can effectively solve this using free Excel RegEx Find/Replace add-in.

(I'm not affiliated with the tool.)

Just enter below formula, and format results as [h]:mm

=SUMPRODUCT(RegExFind(A2,"(\d+)"),{86400,3600,60,1})/86400

enter image description here

2
  • As I understand the way it works, it will return the wrong value if there is no d in the string. Above the OP shows an example of 9hr 37m 41s which will error as the regEx will only return 3 items to the array. Oct 17, 2019 at 20:42
  • Yes, that's right, all parts need to exist. Oct 17, 2019 at 21:05
-1

Combination of few basic Excel function wrapped with ISNUMBER converts the data into desire format.

enter image description here

  • Formula in Cell I31, fill it down. =IF(ISNUMBER(FIND("d",H31)),LEFT(H31,FIND("d",H31)-1)*24)+IF(ISNUMBER(FIND("h",H31)),MID(0&H31,MAX(1,FIND("h",0&H31)-2),2))+MID(0&H31,MAX(1,FIND("m",0&H31)-2),2)/60

  • Apply Appropriate Cell format with 2 Decimals on I31:I35.


Edited:

Since by mistake I've skipped OP's need Results in [H]:mm Time format, therefore I'm posting this new formula, returns Time is desire format.

Readers, just ignore the above shown formula.

enter image description here

  • Formula in Cell I46, fill it down.

    =SUM(MID(0&H46&"000",FIND({"d","h","m"},0&H46&"xxdhm")-2,2)*{1440,60,1})/86400*60

N.B. Apply [h]:mm Time format on I46:I53

  • Formula in Cell J46, fill it down.

    =TEXT(SUM(MID(0&H46&"000",FIND({"d","h","m"},0&H46&"xxdhm")-2,2)*{1440,60,1})/86400*60,"[h]:mm")

Adjust cell reference in the formula as needed.

5
  • 2
    The OP was looking for a [h]:mm time format but you're delivering the minutes as decimal of an hour.\; e.g. 85.67*cannot have *67 minutes in an hour.
    – user385793
    Oct 17, 2019 at 13:12
  • @Jeeped,, my mistake I've missed it !! Oct 18, 2019 at 3:52
  • @Jeeped,,, now check the modified version of my post ☺ Oct 18, 2019 at 4:19
  • That looks better. I wasn't the original down-voter but I've added an upvote to even things out.
    – user385793
    Oct 18, 2019 at 4:55
  • @Jeeped,, not an issue,, thanks for valuable observation. Oct 18, 2019 at 8:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .