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 · Accepted solution]

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. 

[Tommy Strømme]

Ahh..that was just brilliant!!! Thank you so much!!! Awesome!

One final question.. do you know why on earth the date now splits into two lines? In the screnshot is the same table with table filters on top and without on the bottom ( i swapped Green for White (looked better)

Regards

Tommy

[Stiltsoft support]

I think it's because your table is really wide. As there is a space in the date cells and other columns contain a lot of data, Confluence decided to split dates into two lines.

You may set a predefined width for this column though https://docs.stiltsoft.com/tfac/cloud/advanced-table-cells-formatting-58426218.html#Advancedtablecellsformatting-Changingcolumnwidth

[Tommy Strømme]

Hmm.. tried to add as it said there, but if I change it to 80 og 480 it stays the same. No errors though.

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

[Tommy Strømme]

Figured it out!

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

[Stiltsoft support]

Yes, the first variant at first set the width and then rewrote it with the second part of your SQL.

Glad it works for you!