4 Replies Latest reply on Jul 3, 2019 9:07 AM by Alexandros Aliferis

    Difference between dates on different rows (for Jira SLAs)

    Alexandros Aliferis

      Hi all,

       

      I'm struggling to find a solution for this and I'm getting to the point where I don't think there is even a solution for this at all, but I will give the forums a try because I've gotten plenty of help on here before!

       

      So, like the title says above, I am trying to get the difference between the same date in different rows, but I am failing to create a calculation that can accomplish this specific situation.

       

      Each of these dates are "update" dates, detailing when the specific status change was done. I'm using our issues data from our Jira server and trying to replicate our SLAs for time to resolution. For each of these SLAs, there are distinct rules for when time is calculated and when it is not. One of these rules is when a status get's put on the "Pending" or "waiting on customer" status, time is not calculated.

       

      So, in my example data set I have attached, there are a couple of times when the status gets changed to "waiting on customer" and "Pending", so my master plan here is to somehow calculate each time block where time is supposed to not count and subtract that against the total between the "created date" and "resolution date".

       

      So for example, there is a case where the status gets put on "Pending" (History ID: 2858580) at 5/24/2019 9:18:51 AM, but doesn't get changed until 6/25/2019 7:38:10 AM (History ID: 2901069). This means that the time calculated between those two dates (which are the same date fields) would be subtracted from the total.

       

      Hopefully this makes some sort of sense. If it doesn't let me know and I can revise my summary. Thanks!