Table Transformer | Highlighting Dates past due

Tommy Strømme

Hi, 
I have searched the forum and also used chatgpt but cant really get it to work.

I have a table with a date column that is called "Updated".

I simply want to have all dates older then 7 days turn red, otherwise they should stay green. 

This code gives me no errors, but it will list all dates (even those 3 weeks ago) green:

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "#FFFFFF"
WHEN CAST(T1.'Updated' AS DATE) < DATE_SUB(CAST("today" AS DATE), INTERVAL 7 DAY) THEN "#FA7E70"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Can anyone point me in the right direction? Date format is fine btw.

Regards

/Tommy

Stiltsoft support

Hi @Tommy Strømme ,

You may also search the Atlassian Community, there are similar cases with the Table Transformer, for example, this one: https://community.atlassian.com/t5/Confluence-questions/Conditional-Formatting-for-Confluence-tables/qaq-p/1568284

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Updated' IS NULL THEN "Grey"
WHEN DATEDIFF(day,"today",T1.'Updated') <= -7 THEN "Red"
ELSE "Green"
END
+ "}" + FORMATDATE(T1.'Updated') + "{cell}")
AS 'Updated'
FROM T1

Hope it helps your case.