Formula Column, change color of the value calculated

[Alok Kumar]

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 ?

[Dionathan Lopes {ALM Works} · Accepted solution]

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]

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}]

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]

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 :-)

[Dave Rosenlund _Trundl_]

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

Best,

-dave