Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,460,940
Community Members
 
Community Events
176
Community Groups

Excel-like formulas and conditional formatting in Jira? Yes, you can! Here’s how

The Formulas feature in Structure for Jira can uncover valuable metrics in your project data — these shortcuts and solutions show what you can accomplish. 


Have you ever thought, "It'd be nice to…"

  • Perform calculations on or merge Jira field values
  • Compare one field to another
  • Write formulas using Jira fields as variables, to derive new insights
  • Mark overdue items red or completed items green, so they stand out

If you’ve answered “yes” to any of the above, then Structure Formulas might be for you.

As the name suggests, it’s a feature in Structure for Jira — one of the top-selling project management add-ons on the Atlassian Marketplace. The feature lets you write Excel-like formulas so that you can do real-time calculations without leaving Jira, without exporting, and without coding an additional automation script.

That saves you time. You don't have to context-switch, and you might be spared from having to download a CSV file or ask a Jira admin for a new automation rule — the kinds of things that can really bog down daily work.

Instead, all you need to do is insert a column in a spreadsheet-like "structure" and add a Formula column.

Formulas in Structure are written using Expr (like expert, but without the ‘t’), a language with easy-to-learn fundamentals. It relies on Jira data and conditional logic, such as IF statements, and once you get the hang of it, you can easily ramp up the complexity to get the specific insights you need.

Getting started is quite intuitive. You can use the name of the Jira fields in the formulas, like:

IF timeSpent > ogEstimate : "over work estimate"

In this example, timeSpent is a variable that is automatically mapped to a Jira field based on its name. If there is a match, the variable is highlighted in green.

structure-formula-jira-timespent.png

timeSpent is recognized as a variable, but not ogEstimate

If there is no match — like in the case of ogEstimate — the value is highlighted in red and you can then easily map it to an existing Jira field after you click on the variable name.

structure-formula-jira-variable-dropdown.png

Choose the field where you would like to map your variable

Check out these examples of Formulas calculations, to get an idea of how they might help your teams.

 

Compare time spent plus remaining estimate to the original estimate

A simple but efficient way to check if tasks are on track is to use the formula below, which compares time spent plus remaining estimate to the original estimate.

IF timeSpent + remainingEstimate > originalEstimate : "over work estimate"

structure-jira-formula-over-work-estimate.png

A convenient way to identify issues that are over work estimate

 

Count issues that match a certain criteria

Building off of our previous example, sometimes you might not want to print text like “over work estimate” but work with numeric values instead.

Both approaches can easily be done with Formulas. To count issues that match a certain criteria, you can write a formula that outputs a numeric value 1 instead of text.

roll-up-sum-up-values-structure.png
Rolled-up values at the initiative, theme, epic, and story levels

From here, you can easily roll up the values to get a count of items that are “over work estimate” at each hierarchy level by checking the “sum over sub-items” box.

Note that checking the “sum over sub-items” box is the same as wrapping the formula in between the brackets of the SUM{} function:

SUM{ IF timeSpent + remainingEstimate > originalEstimate : 1 }

If you choose to use SUM in the formula, make sure to uncheck the “Sum over sub-items” box to avoid double-counting items.

 

Score any metric you can think of

Structure comes with many built-in formulas like RICE (Reach, Impact, Confidence, and Effort), or WSJF (Weighted Shortest Job First) if you’re working with the Scaled Agile framework. But don’t let these out-of-the-box formulas stop you from experimenting with the Expr language. Once you get the hang of it, you can use it to calculate any metric you can think of.

 

Use wiki markup or markdown for conditional formatting

There’s no better, quicker way to make important things stand out than by calling them out visually.

Our users write various formulas to format output in Jira with wiki markup (if using Data Center and/or Server) or wiki markdown (if using Jira Cloud), so as to understand work at a glance.

We’ve seen users apply conditional formatting to identify high-risk tickets, like in the screenshot below.

high-risk-jira-conditional-formatting.png
High risk issues feat. thundercloud emojis


It uses this formula, which is written using wiki markdown on Jira Cloud:

WITH WORK_TIME_TO_CALENDAR_TIME(time) = (
  WITH min = 60 * 1000:
  WITH hour = 60 * min:
  WITH day = 8 * hour:
  WITH week = 5 * day:
  WITH weeks = FLOOR(time / week):
  WITH r1 = time - weeks * week:
  WITH days = FLOOR(r1 / day):
  WITH r2 = r1 - days * day:
  WITH hours = FLOOR(r2 / hour):
  WITH r3 = r2 - hours * hour:
  WITH mins = r3 / min:
  (((weeks * 7 + days) * 24 + hours) * 60 + mins) * min
):

WITH FORMAT_CAPTION(color, caption) = (
  WITH SPACE(pixels) = """!https://upload.wikimedia.org/wikipedia/commons/5/52/Spacer.gif|width=$pixels!""":
  """:panel[$Caption]{backgroundColor=$color color=white}"""
):

WITH diff = FLOOR((due_date - (TODAY() + WORK_TIME_TO_CALENDAR_TIME(sum {remaining}))) / 86400000):
 
IF issueType = "epic":
IF diff < 0:
  FORMAT_CAPTION("#EF4B59", ":cloud_with_lightning_and_rain:**High Risk** ")
ELSE IF diff < 15:
  FORMAT_CAPTION("#FFAF00", ":sun_behind_large_cloud:;**At Risk**")
ELSE:
  FORMAT_CAPTION("#59B161", ":sunny: **On Track**")

This formula is a basic velocity calculation, based on the due date and remaining estimate at the epic level.

It calculates two things: the work that needs to be done given the remaining estimate – which is rolled up to the epic – and the number of days between “now” and the due date.

If the difference – between the number of days between now and the due date – is over 15 days, things are On Track. If it’s less than 15, it’s At Risk, because the team only has two weeks to sort things out. If it’s less than 0, the epic is marked High Risk, which seems like the case with our sample epics.

A couple of things worth noting:

  • diff is a user-defined variable that represents the difference in number of days
  • 86400000 is the total number of milliseconds in a day (24 hours * 60 minutes * 60 seconds * 1000). Milliseconds is the compatible unit when counting days and duration in Jira – it is the way Jira calculates built-in date and duration fields.

 

Another way we’ve seen users apply conditional formatting is to create a single score with Red Amber Green:

structure-formula-red-amber-green.png
All green for Structure.Gantt

The structure uses this formula, written using wiki markdown on Jira Data Center/Server:

IF progress >= 0.7 :
"{panel:borderStyle=solid|borderColor=white|bgColor=#59B161} !https://d1.almworks.com/.files/weath_icn-01.png|width=20,height=20! !https://d1.almworks.com/.files/Spacer.gif|width=14!{color:white}*Great*{color}!https://d1.almworks.com/.files/Spacer.gif|width=13!{panel}"

ELSE IF progress <= 0.3 :
"{panel:borderStyle=solid|borderColor=white|bgColor=#EF4B59} !https://d1.almworks.com/.files/weath_icn-03.png|width=20,height=20! !https://d1.almworks.com/.files/Spacer.gif|width=7!{color:white}*At Risk* {color}!https://d1.almworks.com/.files/Spacer.gif|width=7!{panel}"

ELSE :
"{panel:borderStyle=solid|borderColor=white|bgColor=#FFAF00} !https://d1.almworks.com/.files/weath_icn-02.png|width=20,height=20! !https://d1.almworks.com/.files/Spacer.gif|width=19!{color:white} *OK* {color}!https://d1.almworks.com/.files/Spacer.gif|width=18!{panel}"


Others even use formulas to gamify their structures, so that issues only get a green score if all of their fields are filled out.


Calculate average time to complete issues

Another way to use formulas is to calculate average time to complete issues, which can come in handy when you want to get a sense of your team’s velocity.

On Jira Cloud, you can use the formula:

AVG{ DAYS_BETWEEN(created, resolved) }

Users on Jira Data Center and Server, where transition columns are supported, can get even more realistic readouts by using the formula:

AVG{ DAYS_BETWEEN(transition_inprogress, transition_done) }

 

structure-formula-average-time.png

Average time to complete issues, in days

 

Horizontally merge story points and time estimate fields into one complete estimate rollup

It’s an all-too-common use case. One team uses Original Estimate, while another uses Story Points. Or maybe all teams use Story Points, but they mean different things depending on the team. One team counts a Story Point as a day, while another counts it as a half-day.

Structure helps resolve such situations by allowing teams to merge different estimates into one estimate rollup.

In the first example of two teams using Original Estimate and Story Points, we could merge the two by using a formula like:

IF storyPoints : 
storyPoints * (3600 * 1000 * 8)
ELSE :
originalEstimate

Here we multiply the story points by milliseconds in an 8-hour day to convert the value to a duration, and by checking sum over sub-items, we can roll up everything into a single estimated value with the original estimate.

See how many sprints an issue has been added to

For the sprint planners, this formula is a one-liner that tells you the number of sprints associated with any given Jira issue.

sprint.SIZE()

 

Compare resolved dates with sprint end dates

You’ve wrapped up many sprints; now it's time for a retrospective. Did team(s) complete a sprint on time, early, or late?

A useful formula for providing these insights is the following formula, which will only return story points of issues which were resolved before the sprint ended:

IF sprint.endDate > resolved : storyPoints

If the sprint end date is past the resolved date, the structure will show the story points for that issue – and roll them up (with sub over sub-items checked), depending on your hierarchy.

If the issue is still unresolved, structure won’t count the story points: they’ll be blank and converted to zero.


But wait…there’s more!

The formulas we’ve included in this article are just the tip of the iceberg. Once you get the hang of the Expr language, you’ll be writing Formulas to meet your team reporting needs in no time.

➡️ Want to write formulas yourself? Find Formulas in Structure, the flexible project management app available for Jira Cloud, Data Center, and Server, and try it free for 30 days.

➡️ Find sample formulas (that you can copy and paste) for Jira Data Center/Server and Jira Cloud. Note that there are some differences between the two versions.

➡️ Are you already using Formulas? We’d love to hear your feedback. Let us know in the comments and feel free to share your favorites!

 

4 comments

Awesome! Structure is getting better and better. 

Like # people like this
Dave Liao Community Leader Aug 04, 2022

@Nicholas Ellis _ALM Works_ - this is an awesome write-up!

I love including emoji on Structure boards - I encourage users to use standard OS-level emoji.

Curious, are the emoji in this write-up embedded in Structure? For example, :sunny:

If so, could a future version of Structure have a blank emoji embedded so there's no need to reference a spacer.gif from an external website?

Like # people like this

Thanks @Dave Liao,

It works slightly differently on different deployments.  On cloud we have a lot of built in emojis, here's the list if you're interested.  On Server/DC we use the Jira built in wiki, which has slightly fewer emoji options.

The other option would be to insert a "■" character and make it the same color as the background, to achieve the same effect.  There are some similar examples here in our documentation.  Although I'm not sure that makes the code above simpler, however removing external dependencies is always a safe bet.  

Like # people like this
Dave Liao Community Leader Aug 04, 2022

@Nicholas Ellis _ALM Works_ - sweet, appreciate the links! 💜💪⚡️

Comment

Log in or Sign up to comment
TAGS

Atlassian Community Events