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

Structure: wiki markup formula with dates

Alvaro G orange

Hi,

I am using the Structure Plugin (ALM Works) and in a column i would like to have a formula that shows the real date when an issue achieve an status having the background coloured in red or green depending if it is late or fine compared with the planned date.

Thanks in advance!!!!

I have found some references like https://community.atlassian.com/t5/Jira-questions/Formula-Column-change-color-of-the-value-calculated/qaq-p/1750811 or https://wiki.almworks.com/documentation/structure/latest/data-center-and-server/wiki-markup-in-formula-columns-57940075.html but i am not able to make it work

My tries are some how like:

IF(PlannedReleaseDate <= ActualReleaseDate, concat("{panel:bgColor=#ADFF2F},ActualReleaseDate,,{panel}"))

IF(PlannedReleaseDate > ActualReleaseDate, concat("{panel:bgColor=##ff5c2f},ActualReleaseDate,,{panel}"))

The issue is that shows always the text ActualReleaseDate instead of the date of the variable ActualReleaseDate.

I can define PlannedRelaseDate as a variable of that date of the issue and ActualReleaseDate as the first to transition "Released" status.

My real wish would be something like:

PlannedReleaseDate >ActualReleaseDate OR PlannedReleaseDate > today() AND ActualReleaseDate is Emply ---> cell in red with ActualReleaseDate (or cell in red without any text)

PlannedReleaseDate <=ActualReleaseDate -> cell in red with ActualReleaseDate

Thank you so much again

Kind regards!!!

Accepted answers

David Niro

Hello @Alvaro G orange ,

David from ALM Works here. Welcome to the Community!

Please give this a try:

IF(issuetype and plannedreleasedate <= actualreleasedate; concat("{panel:bgColor=#ADFF2F}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}"); issuetype and plannedreleasedate > actualreleasedate;concat("{panel:bgColor=#F14925}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}"); issuetype and plannedreleasedate > today()AND actualreleasedate = undefined; concat("{panel:bgColor=#F14925}",Format_Datetime(plannedreleasedate,"dd-MMM-yy"),"{panel}"); issuetype and (plannedreleasedate = undefined AND actualreleasedate = undefined);"")

It includes your real wish scenario

You were very close in your attempt. The only real changes we had to make was around where the quotation marks were in the concat function & making sure to format the date.

Please let me know if this helps, or if you have any questions!

Best Regards,
David Niro
www.almworks.com

All comments

Alvaro G orange

I make it work a much easier example:

IF(fechaPaP > today();
CONCAT(
"{panel:bgColor=#CCFFCC}", FORMAT_DATETIME(fechaPaP,"d-MMM-YY"), "{panel}"
)
)

Getting this:

But, i still need help with the real one

Thanks a lot

David Niro

Hello @Alvaro G orange ,

David from ALM Works here. Welcome to the Community!

Please give this a try:

IF(issuetype and plannedreleasedate <= actualreleasedate; concat("{panel:bgColor=#ADFF2F}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}"); issuetype and plannedreleasedate > actualreleasedate;concat("{panel:bgColor=#F14925}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}"); issuetype and plannedreleasedate > today()AND actualreleasedate = undefined; concat("{panel:bgColor=#F14925}",Format_Datetime(plannedreleasedate,"dd-MMM-yy"),"{panel}"); issuetype and (plannedreleasedate = undefined AND actualreleasedate = undefined);"")

It includes your real wish scenario

You were very close in your attempt. The only real changes we had to make was around where the quotation marks were in the concat function & making sure to format the date.

Please let me know if this helps, or if you have any questions!

Best Regards,
David Niro
www.almworks.com

Alvaro G orange

Hi @David Niro

Thanks a million, you have help me a lot!!!

We have modified a little bit, the latest one we are using is:

IF(
//green for having deployed earlier than planned
issuetype and plannedreleasedate >= actualreleasedate; concat("{panel:bgColor=#ADFF2F}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}");

//red for having deployed later than planned
issuetype and plannedreleasedate < actualreleasedate;concat("{panel:bgColor=#F14925}",Format_Datetime(ActualReleaseDate,"dd-MMM-yy"),"{panel}");

//red for having exceeded the planned deploy date and not having been deployed
issuetype and plannedreleasedate < today()AND actualreleasedate = undefined; concat("{panel:bgColor=#F14925}","Pte. Release","{panel}");

//orange because there are only a few days left for the planned delivery date
issuetype and plannedreleasedate > today() AND DAYS_BETWEEN(today(),plannedreleasedate) <= 10 AND actualreleasedate = undefined; concat("{panel:bgColor=#F17d25}",DAYS_BETWEEN(today(),plannedreleasedate)," día(s)","{panel}");

//yellow because there are only a few days left for the planned delivery date (11-20)
issuetype and plannedreleasedate > today() AND DAYS_BETWEEN(today(),plannedreleasedate) > 10 AND DAYS_BETWEEN(today(),plannedreleasedate) <= 20 AND actualreleasedate = undefined; concat("{panel:bgColor=#FFFF21}",DAYS_BETWEEN(today(),plannedreleasedate)," día(s)","{panel}");
"")

This tool is amazing. We can have a great view of the status of the projects with a few columns that compares planned and actual dates for a few milestones..

Thanks again!!!!
Best regards,

Welcome!

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

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

jira

cloud

jira-cloud

structure

markup

formula