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.
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.
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"
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.
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 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:
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) }
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!
Was this helpful?
Thanks!
Nicholas Ellis _ALM Works_
About this author
Senior Solutions Engineer
ALM Works
13 accepted answers
4 comments