Struggling with date format in table transformer

[Andy Knott]

I understand that table transformer can be used to format colours of rows when an expression is evaluated. and I'm successfully understanding how to do that with string data - but I'm struggling to understand te approach when the field being formatted contains a date element.

So the problem is to evaluate a field, my intent is to check the data field and then highlight those rows where the date has been missed 

When  I run this code, what happens is the date field is populated with a 13 digit integer rathe than the original date value that was in the cell - I have confirmed that the table transformer and the table have the same date structure, ie d m yy.

Any pointers will be appreciated as I'm finding this rather frustrating. 

FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Task' + "{cell}")
AS 'Task',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Dates' + "{cell}")
AS 'Dates',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Owner' + "{cell}")
AS 'Owner'
FROM T1

[Stiltsoft support · Accepted solution]

Hi @Andy Knott ,

Please add the FORMATDATE function before calling the 'Dates' column:

...
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + FORMATDATE(T1.'Dates') + "{cell}")
AS 'Dates'
...

This query should be working fine.

[Andy Knott]

Perfect thanks very much

[Stiltsoft support]

Hi @Andy Knott ,

Another option is to use commas instead of pluses inside the FORMATWIKI functions: it helps to preserve not only dates automatically but the original cells' contents in general (clickable links, text style, etc.).

Please check an example from our documentation (navigate to the "Preserving initial cell formatting" section).