nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Conditional Formatting for Confluence tables

Pansilu Diegayu Wickramasinghe

I want to format a column in my table so that the dates passing today would turn red and if the date is closer than or equal to 6 months from now it would turn yellow. I'm not too sure about how to do this on confluence. If there's a method you can help me out with, I'd really appreciate it. Thank you!

Accepted answers

Katerina Kovriga _Stiltsoft_

Hi @Pansilu Diegayu Wickramasinghe ,

You may try the Table Filter and Charts for Confluence app and its Table Transformer macro with the advanced cells formatting feature.

Wrap your table in the Table Transformer macro:

Mon 1-1.png Set an appropriate date format:

Mon 1-2.png And use the following custom SQL query to count the period between two dates and format your cells:

SELECT T1.'Project Name',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Dates') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Dates') > "0" AND DATEDIFF(day,"today",'Dates') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Dates' + "{cell}")
AS 'Dates'
FROM T1

Mon 1-3.png Mon 1-4.png Hope this may help your case.

All comments

Brant Schroeder

Pansilu,

Welcome to the community. The only way I know of to do this in Confluence Cloud is using an app like CelesteCS Conditions for Confluence. On server you can write a user macro but in cloud I believe an app is the only option you have.

Katerina Kovriga _Stiltsoft_

Hi @Pansilu Diegayu Wickramasinghe ,

You may try the Table Filter and Charts for Confluence app and its Table Transformer macro with the advanced cells formatting feature.

Wrap your table in the Table Transformer macro:

Mon 1-1.png Set an appropriate date format:

Mon 1-2.png And use the following custom SQL query to count the period between two dates and format your cells:

Mon 1-3.png Mon 1-4.png Hope this may help your case.

Shardul Juyal {Addteq}

You can try our app Excellentable to create conditional formats just the way you do them in Excel/Google sheets.
You can also import a .xlsx file with conditional formatting directly to Excellentable.

Here is an example

Pansilu Diegayu Wickramasinghe

Hello there,

Thank you for your response, I have a quick question. Why have you mentioned 'Project Name' after selecting T1? In my case should I write 'Certification' ?

Also I have multiple tables like this that needs the same formatting, does that mean I should type in 'T*' instead of specifying the table?

Katerina Kovriga _Stiltsoft_

If you want all the columns from your table to be displayed, than use an asterisk:

SELECT *,
FORMATWIKI ...

If you want to show only specific columns, than list them:

SELECT T1.'Certification', T1.'Jake' ...

In the end of your SQL query you can type T1 or T* - it makes no difference because you have only one table inside your Table Transformer macro.

But you need to use a specific column name while applying conditional formatting ('Dates' for my case and, for example, 'Kev' for yours).

Pansilu Diegayu Wickramasinghe

Hello there, since I have more than one column that needs conditional formatting, should I separate it with a comma like this

CASE WHEN DATEDIFF(day,"today",Jake', 'Kev', 'Dave') <= "0" THEN "#FA7E70"

Or should I rewrite the code for every column?

Katie A

@Katerina Kovriga _Stiltsoft_

This Q/A was another awesome one, but I have a followup question. Can you use the FORMATWIKI to also add hover text?

Following this Q/A I have this:

SELECT
T1.'Task appears on',
T1.'Assignee',
FORMATWIKI("{cell:color=" +
CASE WHEN DATEDIFF(day,"today",T1.'Due date') <= "0" THEN "#E8330C|font-weight=bold"
WHEN DATEDIFF(day,"today",T1.'Due date') > "0" AND DATEDIFF(day,"today",T1.'Due date') <= "7" THEN "#E8910C|font-weight=bold"
WHEN DATEDIFF(day,"today",T1.'Due date') > "7" AND DATEDIFF(day,"today",T1.'Due date') <= "30" THEN "#E8DB0C|font-weight=bold"
ELSE "#8FCA7D"
END
+ "}" + T1.'Due date' + "{cell}") AS 'Due Dates',
T1.'Label(s)',
FROM T1