nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formula Column, change color of the value calculated

Alok Kumar_vkf9tq

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 ?

Accepted answers

Dionathan Lopes {ALM Works}

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]

All comments

Dionathan Lopes {ALM Works}

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_vkf9tq

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_vkf9tq

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

structure

markup

colour

formula