nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

Brendan Tipney

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

All comments

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

Brendan Tipney

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



=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(

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

Andrew Harrell

Just had to solve this problem, and your solution worked great! Thanks for sharing.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

jira-service-management-cloud

jira-service-management