I've been given an Excel spreadsheet with a "Time Spent" column that has values in the standard Jira time tracking format, splitting into days, hours and minutes:
Time Spent (Jira format)
------------------------
1d 7h 30m
30m
20d 5m
It can't be regenerated - it was from a particular point in time and the figures have moved on. I need to convert these times into just the number of minutes - e.g. the results from above should be:
Time Spent (minutes)
--------------------
1890
30
28805
This needs to all happen in Excel, ideally using just a formula that would work for all such time spans - would prefer to avoid VBA if possible.
Break it down into steps.
How many Days? If it contains "d", then we want the characters to the left of the "d": (Multiply by 24 for hours or 1440 for minutes)
IF(ISERROR(FIND("d",A1)),0,LEFT(A1,FIND("d",A1)-1))
How many Hours? Well, if it contains "h", then we want the number to the left of the "h". If we grab the 2 characters to the left, that will either be a 2-digit number, or a space and a 1 digit-number. We can then use TRIM to strip the extra spaces: (Again, multiply by 60 for minutes)
IF(ISERROR(FIND("h",A1)),0,TRIM(MID(A1,FIND("h",A1)-2,2)))
How many Minutes? That's basically the same as for Hours, but looking for "m" instead of "h" - and no need to convert it either.
IF(ISERROR(FIND("m",A1)),0,TRIM(MID(A1,FIND("m",A1)-2,2)))
Stick it all together, with the conversions to minutes included this time:
=IF(ISERROR(FIND("d",A1)),0,1440*LEFT(A1,FIND("d",A1)-1))+IF(ISERROR(FIND("h",A1)),0,60*TRIM(MID(A1,FIND("h",A1)-2,2)))+IF(ISERROR(FIND("m",A1)),0,TRIM(MID(A1,FIND("m",A1)-2,2)))
{EDIT} Updated code for in case the string starts with single-digit minutes/hours:
=IF(ISERROR(FIND("d",A1)),0,1440*LEFT(A1,FIND("d",A1)-1))+IF(ISERROR(FIND("h",A1)),0,60*TRIM(MID(" "&A1,FIND("h",A1)-1,2)))+IF(ISERROR(FIND("m",A1)),0,TRIM(MID(" "&A1,FIND("m",A1)-1,2)))