Hello everyone,
I'm facing an issue when extracting data from Jira Service Desk, my ultimate goal is to calculate the SLA average. I did the extract in CSV file but the format of duration gives me two challenges :
- An SLA duration can be negative (when exceeding the expected SLA)
- Digit for hours can be above 24h
I tried to play with Excel format cell options but I wasn't able to format my columns in a satisfying way.
I would appreciate any help!
@lemoi66 I'm not familiar with Jira, but if you connect to the CSV with, e.g. Power Query, you can at least make sure that the time references in it get transformed to proper time values, so that you can calculate duration. Your picture suggests that the column "Time to first response" actually contains texts.
Default Excel settings don't allow for negative time. You need to switch to the 1904 date system to do that. Don't ask why, but then you can work with negative time/duration (see picture below).
Once you have your Time texts transformed to proper Time values (i.e. numbers), you can use custom formatting to achieve the "simplified" format that you describe.
More about the 1904 date system in the link below:
https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system