Formatting timetracking output into a multi-line text field with Jira automation and smartvalues.

Ward Schwillens_ Schwillie

Hi,

I'm running an automation in Jira Cloud. 

This automation summarizes the Original Estimate, the Time Spent and the Remaining Estimate of an EPIC, it's children and their descendants and puts it in a table in a multi-line text field. 

Currently the output looks like this. 

Now I want to format the output a bit more. 

1) as number with 2 decimals the 19.583333333332 should be shown as 19.58

2) as time, where the 65.25 should be shown as 2d 17h 15m 

Here's the automation setup:

And here's the edit issue. 

Here's the edit issue in text:

|| || Original Estimate || Time Spent || Remaining Estimate ||
|| Totals || {{#=}} {{lookupIssues.timetracking.originalEstimateSeconds.sum}}/3600{{/}}|| {{#=}} {{lookupIssues.timetracking.timeSpentSeconds.sum}}/3600{{/}}|| {{#=}}{{lookupIssues.timetracking.remainingEstimateSeconds.sum}}/3600{{/}}||

Don't mind the "||" characters here, these are used to format a table in the output to the multi-line text field in a wiki-renderer field configuration.

 

Now I tried things like:

{{lookupIssues.timetracking.originalEstimateSeconds.sum.divide(3600).format(".##"}}

and

{{lookupIssues.timetracking.originalEstimateSeconds.sum.divide(3600).format("###.##"}}

and this gave me a number output without any decimals.

Then I found the solution using the ROUND function:

{{#=}} ROUND(({{lookupIssues.timetracking.originalEstimateSeconds.sum}}/3600),2){{/}}

{{#=}} ROUND(({{lookupIssues.timetracking.timeSpentSeconds.sum}}/3600),2){{/}}

{{#=}} ROUND(({{lookupIssues.timetracking.remainingEstimateSeconds.sum}}/3600),2){{/}} 

This gave me what I wanted in the first option: 

But what can I do to get a "Time-formatted" output like 2d 17h 15m ?

 

Thank you for your insights, I hope that my input also helps others trying to format their outputs. 

@Bill Sheboy I hope the pictures and documentation in this question are sufficient. Thanks! 😉

Bill Sheboy

Hi @Ward Schwillens_ Schwillie 

For the first question, you may try the format() function with a mask to get that 19.58: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/#format-input-  Another approach would be to use math and the floor or ceil functions.

The second question for pretty printing a number of seconds into days, hours, is more complicated, and will require some assumptions.

Let's assume there are 24h in a day (and so not working hours).  That could be formatted by adding the number of seconds to {{now}} and performing a diff on itself, like this, and using the prettyPrint difference format: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-difference---

{{now.diff(now.plusSeconds(lookupIssues.timetracking.timeSpentSeconds.sum)).prettyPrint}}

Outputting in business time (e.g., 8 hours / day) would require performing more math operations to manually build the expression, or...

 

Update: I figured out a way to pretty print 8 hour days, in one step:

{{now.diff(now.plusSeconds(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3).multiply(28800).plus(lookupIssues.timetracking.timeSpentSeconds.sum.minus(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(28800))))).prettyPrint}}

How that mess works :^)

• The logged time sum can be grabbed in seconds, which people normally enter in working time, not calendar time.
• Let's assume an 8 hour work day, and so the "trick" is to separate the whole 8h days, scale that up to 24h days, add in any partial days, and we have an adjusted total
• Once we have that value, it can be added to any date / time, such as {{now}} to perform a difference calculation...leading to a pretty print option
• To break down the above expression further...

(A) Total time spent seconds = 
    lookupIssues.timetracking.timeSpentSeconds.sum

(B) Seconds in an 8h day = 28800

(C) Dividing those gives the 8h days: 
    lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800)

(D) But we want the whole days only, so we add on floor:
    lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor

(E) Each 24h day has the equivalent of 3 x 8h, so we multiply by 3:
    lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3)

(F) And finally multiply again by 28800 to get back to seconds:
    lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(3).multiply(28800)


(G) We now need to find the partial 8h so we can get the hours and minutes.  To find that, we need to subtract the seconds in whole-8h days from the original total
    lookupIssues.timetracking.timeSpentSeconds.sum.minus(lookupIssues.timetracking.timeSpentSeconds.sum.divide(28800).floor.multiply(28800))

(H) Adding together (F) and (G) we have the correct total seconds.

(I) Finally returning to the original technique, we just add these to
    the current date time to perform a diff, and the pretty print.

Whew!

Kind regards,
Bill