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!!!

[David Niro · Accepted solution]

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,

[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