Working with SLA times in Google Sheets

[Brendan Tipney]

I am having an issue working with SLA values when brought into Sheets. The problem is that the values come in as 6h 21m. To make it harder, they may come in as just 21m if no hour component is present, or -21m if the SLA has expired. Is there any way to get this value in a time format that is easier to work with in sheets?

[Brendan Tipney · Accepted solution]

Yes, in the end I was able to get the data into Hour format using some Regular expressions.

The below expression was able to pull out the hours and convert minutes to partial hours. It also sets cells to blank if they do not contain data. Just change K3 in the code to the cell you are looking to convert.

=IF(ISBLANK(K3),,IF(REGEXMATCH(K3,"[h]"),IF(REGEXMATCH(K3,"[m]"),(REGEXEXTRACT(K3,"(\d+)h") + REGEXEXTRACT(K3,"(\d+)m")/60),REGEXEXTRACT(K3,"(\d+)h")),REGEXEXTRACT(K3,"(\d+)m")/60))

[Maksym Yaremko]

Error(

[Mohamed Benziane]

Hi,

You could create a in your sheet to transform the value in second or minute. You may need to separate your SLA in multiple column (hour, minute, second).

[Earl McCutcheon]

Hello All,

I was just playing around with a similar function and found a solution to this one.  Sharing the solution in case anyone else is looking to do something similar.

Assuming Cell A2 is the cell that contains the Jira SLA value using the following formula did what I needed:

=((iferror(left(A2,search("h",A2)-1),0))*3600) + ((iferror(iferror(MID(A2, SEARCH("h", A2)+1, SEARCH("m", A2) - SEARCH("h", A2) -1),left(A2,search("m",A2)-1)),0))*60)

This will take any combination of Positive or negative values where there is an hour and minute combination OR just the hour OR Just the Minute (for the edge case where a SLA was exported with exactly an hour or only minutes remaining) 

so if it is "10h 1m" or "1h 10m" or "1h" or "1m" or "-999999999h 2m" etc.... and converts it into seconds which is a much easier time format to use for later conversions in Spreadsheet applications.

I know this is an older post but Hope this reaches you or anyone who runs across this in the future.

Regards,
Earl