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 -->
)
)
{{/=}}

[Bill Sheboy · Accepted solution]

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

[Gary Lazaro]

Thanks Bill!  That made things much, much simpler and easier and has got it working!

This is what I'm currently doing:
{{#=}}
({{#lookupIssues}}{{timetracking.originalEstimateSeconds | 0}}{{^last}} + {{/}}{{/}})
-
({{#lookupIssues}}{{timetracking.timeSpentSeconds | 0}}{{^last}} + {{/}}{{/}})
{{/=}} / 3600

The returned value is rounding to three decimal places.  Would you know how to round the returned value to 1 or 2 decimal places?

Many thanks,
Gary

[Bill Sheboy]

Hi @Gary Lazaro 

You may add the ROUND() function around your iteration and inside of the math expression.  For example, this rounds to 2 digits.  Please note I moved the division by 3600 inside the math expression before the rounding.

{{#=}}ROUND( ( ( {{#lookupIssues}}{{timetracking.originalEstimateSeconds | 0}}{{^last}} + {{/}}{{/}} ) - ( {{#lookupIssues}}{{timetracking.timeSpentSeconds | 0}}{{^last}} + {{/}}{{/}} ) ) / 3600, 2 ){{/=}}

[Gary Lazaro]

Brilliant.  Thanks again Bill!

[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! 

[Gary Lazaro]

Hi Walter,

Thanks for the suggestion, and agreed, using the remaining estimate from child issues is a common approach.

However, we’ve found it doesn’t always represent the true picture, particularly when time logged exceeds the original estimate. Here's a quick example to illustrate:

Scenario

Total Project Estimate: 100 hours
Child Tasks:

• Task A: Original Estimate = 75h
• Task B: Original Estimate = 15h
• Task C: Original Estimate = 10h

Logged Time:

• Task A: Time Spent = 95h
• Task B: Time Spent = 5h
• Task C: Time Spent = 10h

Jira’s remainingEstimateSeconds Output

• Task A: 0h (because time spent exceeded estimate)
• Task B: 10h
• Task C: 0h
  → Total Remaining = 10h

Actual Work Remaining (Using Original Estimate - Time Spent)

• Task A: 75h - 95h = -20h
• Task B: 15h - 5h = 10h
• Task C: 10h - 10h = 0h
  → Total Remaining = 0h (or -10 if possible)

This manual calculation gives us a clearer picture of overrun and remaining effort. We want to highlight when a task has exceeded scope, not just zero it out. So our approach is to calculate Original Estimate - Time Spent, allowing for negative values if necessary.

Hope that helps clarify why we’re not relying solely on Jira’s built-in remainingEstimateSeconds.

Thanks again,
Gary