Need Date formatting in Structure Formula with Wiki Markup

[Jozef Vandenmooter]

Does anyone know how to convert the numeric Date and Time field to a readable format?

I don't see a function that allows that conversion on the Expr reference page.

I have the following in a Formula column with Wiki Markup, and the coloring works fine, but the DueDate value (mapped to a Jira custom field of type Date Picker) is displayed as a number. 

WITH setcolor(value, color) = """{color:$color}$value{color}""": 
IF (
DAYS_BETWEEN(TODAY(), DueDate) < 1, setcolor(DueDate, "#FF0000"), //Red
DAYS_BETWEEN(TODAY(), DueDate) > 0 and DAYS_BETWEEN(TODAY(), DueDate) < 8 , setcolor(PID, "#0000FF"), //Green
DAYS_BETWEEN(TODAY(), DueDate) > 7, setcolor(DueDate, "#27AE60") //Blue
)

Thanks,

Jozef  

[David Niro · Accepted solution]

Hello @Jozef Vandenmooter ,

David from ALM Works here.

You will want to use the Format_Datetime function for this.  

It will look something like this :

setcolor(format_datetime(PID,"MM/dd/yy"), "#0000FF")

I also adjusted the full formula here:

WITH setcolor(value, color) = """{color:$color}$value{color}""": 
IF (
DAYS_BETWEEN(TODAY(), DueDate) < 1, setcolor(format_datetime(DueDate,"MM/dd/yy"), "#FF0000"), //Red
DAYS_BETWEEN(TODAY(), DueDate) > 0 and DAYS_BETWEEN(TODAY(), DueDate) < 8 , setcolor(format_datetime(PID,"MM/dd/yy"), "#0000FF"), //Green
DAYS_BETWEEN(TODAY(), DueDate) > 7, setcolor(format_datetime(DueDate,"MM/dd/yy"), "#27AE60") //Blue
)

It uses SimpleDateFormat, so if you do not prefer the very American format I used, you can always adjust :) .

Let me know if it helps!

Best,
David

[Jozef Vandenmooter]

Works - thanks!

Jozef