Calculate Remaining Time (in hours) from Original Estimate and Time Spent

[Gary Lazaro]

Hi Brains Trust,

I've been working at this for over a day now, even having run it past chatgpt without much joy.

I'm trying to calculate the project hours remaining by calculating all of the original estimated at the task level, then deducting that from the time spent at the task level.  Then rolling this figure up to display in a custom field at the epic level.

This is a formula that calculates the time remaining on a task by:

1. Taking the original estimated time for the task (which can be given in weeks, days, hours, and minutes),

2. Converting all those time units into seconds,

3. Doing the same for the time already spent on the task (also in weeks, days, hours, and minutes),

4. Subtracting the time spent (in seconds) from the original estimate (in seconds).

5. Converting this to hours, then writing back to the epic in a custom field.

So basically:

• It looks at the original estimate like "2w 3d 4h 15m" and converts everything to seconds.

• It looks at the time spent like "1w 1d 2h 30m" and also converts to seconds.

• Then it subtracts time spent from the original estimate to find how many seconds are left.

If any part (weeks, days, hours, minutes) is missing, it treats that as zero.

The challenge is that the the characters found in the time amounts, (ie. w, d, h, m).  These need to be accounted for so that the calculation works.

Here's what I have so far (I haven't yet divided the entire calc by 3600 yet as I want to see what's going on first):

{{#=}}
(
(
{{#if(issue.timetracking.originalEstimate.match("([0-9]+)w") != null)}}
{{issue.timetracking.originalEstimate.match("([0-9]+)w").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 604800 + <!-- weeks to seconds -->
{{#if(issue.timetracking.originalEstimate.match("([0-9]+)d") != null)}}
{{issue.timetracking.originalEstimate.match("([0-9]+)d").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 86400 + <!-- days to seconds -->
{{#if(issue.timetracking.originalEstimate.match("([0-9]+)h") != null)}}
{{issue.timetracking.originalEstimate.match("([0-9]+)h").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 3600 + <!-- hours to seconds -->
{{#if(issue.timetracking.originalEstimate.match("([0-9]+)m") != null)}}
{{issue.timetracking.originalEstimate.match("([0-9]+)m").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 60 <!-- minutes to seconds -->
)
-
(
{{#if(issue.timetracking.timeSpent.match("([0-9]+)w") != null)}}
{{issue.timetracking.timeSpent.match("([0-9]+)w").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 604800 + <!-- weeks to seconds -->
{{#if(issue.timetracking.timeSpent.match("([0-9]+)d") != null)}}
{{issue.timetracking.timeSpent.match("([0-9]+)d").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 86400 + <!-- days to seconds -->
{{#if(issue.timetracking.timeSpent.match("([0-9]+)h") != null)}}
{{issue.timetracking.timeSpent.match("([0-9]+)h").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 3600 + <!-- hours to seconds -->
{{#if(issue.timetracking.timeSpent.match("([0-9]+)m") != null)}}
{{issue.timetracking.timeSpent.match("([0-9]+)m").get(0).replaceAll("[^0-9]", "").asNumber}}
{{else}}0{{/}} * 60 <!-- minutes to seconds -->
)
)
{{/=}}

[Walter Buggenhout]

Hi @Gary Lazaro, is there any reason why you can't simply use the remaining estimate from your child work items to calculate the remaining estimate for your epic?

Your rule may turn out somewhat like this:

I used a Lookup work items action to fetch all child items from the epic and - in this case - add a comment to the epic with the calculated smart value from that list. The result is returned in seconds, hence the / 3600 part to turn the result into hours.

Hope this helps! 

[Bill Sheboy]

Hi @Gary Lazaro 

I recommend not trying to manually parse the "pretty print" version of the time tracking values, and instead use the built-in seconds values, dividing when needed to get other units:

{{issue.timetracking.originalEstimateSeconds}}

{{issue.timetracking.remainingEstimateSeconds}}

{{issue.timetracking.timeSpentSeconds}}

To find smart values like this, please use this how-to article to view the data available to rules: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

Kind regards,
Bill