Use Excel to parse text to hours

I use a tool that displays elapsed time in the format of Xd Xh Xm, for days-hours-and minutes. If it's less than 1 day, then the output is Xh Xm. It's easy for me to scrape the output, but I need it in hours to manipulate it further.

Example outputs and converted results:

• 2d 0h 18m = 48.3 (in hrs)
• 14h 9m = 14.15 (in hrs)

I wrote an Excel function to accomplish this task.

Assuming my output is in cell B3, this is the function.

=IF(ISERROR(FIND("d",B3)),LEFT(B3,FIND("h",B3)-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60),(LEFT(B3,FIND("d",B3)-1)*24)+MID(B3,FIND(" ",B3,FIND("d",B3))+1,FIND("h",B3)-FIND(" ",B3,FIND("d",B3))-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60))

Notice it starts with an IF statement. The IF statement with ISERROR checks to see if the text being parsed begins with 'd' for days or 'h' for hours. Once this is resolved, there are just a couple simple things to do.

• FIND is used to locate a specific letter in the string.
• For the first value, use LEFT to parse, and get the value up to the letter 'd' (or 'h' when 'd' isn't present). LEFT needs to know which cell you're checking (B3), and how many characters to grab from the beginning. FIND helped us determine how many characters in until we reached 'd' so we know we need to go when character less.
  LEFT(B3,FIND("d",B3)-1)
• MID is used to get subsequent values. MID requires the cell reference (B3),  where to start, and the number of characters. We want to start after the prior letter, i.e. if we're searching for the minute (m) value, we'd start after hour (h) + 1.  We add an additional 1 to compensate for the space after the prior letter (h).
  The number of characters is easy, it's the location of the designators letter (e.g. 'm') - the start location - 1. Remember the designator letter (m) is one more character than the actual number, hence we have to subtract 1 as part of our formula.
  MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1
• We can't forget about converting our days and minutes to hours. So when we parse the value for days, we multiple by 24, and for minutes, we divided by 60.
  (LEFT(B3,FIND("d",B3)-1)*24)
  (MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60
• Remember --
• You need to add them together.
• You need 2 versions: 1 for the condition where the day (d) is included and 1 when the day (d) isn't included.

TIP: Whenever I build complex functions like this, I first build smaller pieces and then combine them together.