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

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Formula Column, change color of the value calculated

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 ?

1 answer

1 accepted

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]

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

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]

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you