Formula Column, change color of the value calculated

Alok Kumar July 15, 2021

Hi Team,

 

I have a structure where I have two dates column (e.g. initial committed date & actual delivery date). Now I want to add a formula column (lets call it Deviation) in which I want to populate the difference between the two dates and based on condition I wanted to use color for the dates calculated. Eg. 

IF ( DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 30 then this Deviation column will show the difference in days but in Red color (not in black)

initial_committed_date = 31/August /2021

actual_delivery_date = 31/October / 2021

Deviation = 60 (but in red color not in black)

What I tried with wiki markup can only print text and I cannot do the calculation within it, if I add formula as:

IF ( DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 30, "{color:Red} DAYS_BETWEEN(initial_committed_date, actual_delivery_date) {color}", this will just print "DAYS_BETWEEN(initial_committed_date, actual_delivery_date)" in red color but not the actual difference value (which I can understand because of double quotes), which I want.

 

My ask is it possible in Jira to have color formatting and actual calculated value ?

2 answers

1 accepted

2 votes
Answer accepted
Dionathan Lopes _ALM Works_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
July 15, 2021

Hello Alok,

Thank you for sharing your Structure Formulas question here on the Atlassian Community

You could try the below formula and see if it works:

IF(
DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 30;
CONCAT("{color:Red}", DAYS_BETWEEN(initial_committed_date, actual_delivery_date), "{color}")
)

You would need to change the Format to Wiki markup, by selecting it on the dropdown list right next to the "Options" field. Please refer to this article for more information - LINK  

Please let me know if it helps and don't hesitate to contact us if you need any further assistance.

Best Regards,
Dionathan Lopes
[ALM Works]

Alok Kumar July 19, 2021

Thanks Lopes, it does work.

can you help me if I have multiple condition, how I need to break this down

 

IF(
DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 30;
CONCAT("{color:Red}", DAYS_BETWEEN(initial_committed_date, actual_delivery_date), "{color}"),

(DAYS_BETWEEN(initial_committed_date, actual_delivery_date) < 30 and DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 10) ;
CONCAT("{color:Orange}", DAYS_BETWEEN(initial_committed_date, actual_delivery_date), "{color}"),

)

 

This not seems to be working

Dionathan Lopes _ALM Works_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
July 19, 2021

Hello Alok,

Absolutely!  Please try the following:

IF(
DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 30;
CONCAT("{color:Red}", DAYS_BETWEEN(initial_committed_date, actual_delivery_date), "{color}");
DAYS_BETWEEN(initial_committed_date, actual_delivery_date) < 30 and DAYS_BETWEEN(initial_committed_date, actual_delivery_date) >= 10;
CONCAT("{color:Orange}", DAYS_BETWEEN(initial_committed_date, actual_delivery_date), "{color}")
)

Please let me know if it helps.

Best Regards

Dionathan Lopes

[ALM Works]

Alok Kumar July 20, 2021

Hi Dionathan,

Thanks for help, it does work. In the mean time I also found a solution

with difference = DAYS_BETWEEN(initial_committed_date, actual_delivery_date):
(IF ( difference >= 90, """{color:Red}$diff{color}""",
((difference >= 60 AND difference <90)),"""{color:Orange}$diff{color}""")

 

also works like charm :-)

0 votes
Dave Rosenlund
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 7, 2021

@Alok Kumar: You may be interested in the upcoming Structure 7 / Formulas / Expr 2 Bootcamp.

Best,

-dave 

Suggest an answer

Log in or Sign up to answer