CODEWITHSUNDEEP
excelexcel-formulajiratime-formatelapsedtime
Steve Chambers
Steve Chambers

Reputation: 39394

How to convert Jira elapsed time format into just minutes in an Excel formula?

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.

Upvotes: 2

Views: 1790

Answers (4)

Gary Timuss
Gary Timuss

Reputation: 11

None of the previous answers worked for me when data included weeks or minutes, with arbitrary number of digits, so I revamped the previous answers to get

=SUM(SUMIF(REGEXEXTRACT(A2, "(\d+)w") * 40, ">#N/A")) + SUM(SUMIF(REGEXEXTRACT(A2, "(\d+)d") * 8, "<>#N/A")) + SUM(SUMIF(REGEXEXTRACT(A2, "(\d+)h") * 1, "<>#N/A")) + SUM(SUMIF(REGEXEXTRACT(A2, "(\d+)m") /60, "<>#N/A"))

Upvotes: 1

Rob W
Rob W

Reputation: 9142

I just had to write a formula to parse "w", "d", "h" delimiters from JIRA estimates. For example "4w 2d 3h" in a column. The result was this:

=if(C2 <> "", SUM(SUMIF(REGEXEXTRACT(C2, ".*([\d]+)w.*") * 40, "<>#N/A"), SUMIF(REGEXEXTRACT(C2, ".*([\d]+)d.*") * 8, "<>#N/A"), SUMIF(REGEXEXTRACT(C2, ".*([\d]+)h.*"), "<>#N/A")), "")

It's probably best you don't look too hard into it. This uses regular expressions, so beware (in case your column has other data).

Upvotes: 0

Hakan ERDOGAN
Hakan ERDOGAN

Reputation: 1210

When your data is on Col:A starting from A3, you may use this formula and copy down:

=IFERROR(MID($A3,1,FIND("d",$A3)-1),0)*24*60+IFERROR(MID($A3,FIND("h",$A3)-IF(FIND("h",$A3)<3,1,2),IF(FIND("h",$A3)<3,1,2))*60,0)+IFERROR(MID($A3,FIND("m",$A3)-IF(FIND("m",$A3)<3,1,2),IF(FIND("m",$A3)<3,1,2)),0)

Upvotes: 3

Chronocidal
Chronocidal

Reputation: 7951

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)))

Upvotes: 4

Related Questions