Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Conditional Formatting for Confluence tables

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!

3 answers

1 accepted

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.pngSet an appropriate date format:

Mon 1-2.pngAnd 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.pngMon 1-4.pngHope this may help your case.

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?

1232323.PNG

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).

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?

Also I keep getting an output like this for some reason, it doesn't display the date, but some random numbers. Can you tell me why?

Thank you!

44444444444444444.PNG

For the first question: you should apply conditional formatting separately for every required column ('Jake', 'Kev' and 'Dave'). You compare every column date with "today" so you can't list several columns at once via commas.

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

Wed 1-1.png

For the second question: make sure that your date format corresponds the fornat inside the Table Transformer macro (I've shown it in my very first answer).

And you shouldn't have the 'Dates' column at all - it was from my example, so you didn't change the SQL query. Now I've given the whole SQL query for you table, so you may just copy it.

@Katerina Kovriga _Stiltsoft_  I seems to have come across a problem with the table when I published it.

Can you tell me why. Thanks

11111.PNG

Hi, please, check again your SQL query (you should compare every column with "today" separately) and the date format in the Table Transformer macro (Settings -> Date format, it should correspond the date format inside your original table).

To find a mistake, we need to see screenshots of your original table and the Settings tab of the Table Transformer macro along with a copy of your SQL query.

Maybe it will be better to raise a support ticket and attach everything there not to show any confidential data in the Community.

Hello @Katerina Kovriga _Stiltsoft_ 

I've attached the screenshots and the code below.

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Jack') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jack') > "0" AND DATEDIFF(day,"today",'Jack') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Jack' + "{cell}")
AS 'Jack',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Nick') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Nick') > "0" AND DATEDIFF(day,"today",'Nick') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Nick' + "{cell}")
AS 'Nick',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Jake') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jake') > "0" AND DATEDIFF(day,"today",'Jake') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Jake' + "{cell}")
AS 'Jake',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Kev') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Kev') > "0" AND DATEDIFF(day,"today",'Kev') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Kev' + "{cell}")
AS 'Kev',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Nisal') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Nisal') > "0" AND DATEDIFF(day,"today",'Nisal') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Nisal' + "{cell}")
AS 'Nisal',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Paul') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Paul') > "0" AND DATEDIFF(day,"today",'Paul') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Paul' + "{cell}")
AS 'Paul',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Dave') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Dave') > "0" AND DATEDIFF(day,"today",'Dave') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + T1.'Dave' + "{cell}")
AS 'Dave'
FROM T*

02.PNG

01.PNG03.PNG

I think there's a problem with the app because I edited the way I entered the date. Instead of 2/25/2020 under Kev I entered 02/25/2020, and it seems the problem is fixed but this isn't the case if I want to enter a date in November (eg: 11/11/2018)

04.PNG05.PNG

@Pansilu Diegayu Wickramasinghe , sorry it's my bad: forgot about the FORMATDATE expression in the SQL query.

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

Mon 1-1.png

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Jack') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jack') > "0" AND DATEDIFF(day,"today",'Jack') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Jack') + "{cell}")
AS 'Jack',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Nick') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Nick') > "0" AND DATEDIFF(day,"today",'Nick') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Nick') + "{cell}")
AS 'Nick',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Jake') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jake') > "0" AND DATEDIFF(day,"today",'Jake') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Jake') + "{cell}")
AS 'Jake',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Kev') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Kev') > "0" AND DATEDIFF(day,"today",'Kev') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Kev') + "{cell}")
AS 'Kev',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Nisal') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Nisal') > "0" AND DATEDIFF(day,"today",'Nisal') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Nisal') + "{cell}")
AS 'Nisal',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Paul') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Paul') > "0" AND DATEDIFF(day,"today",'Paul') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Paul') + "{cell}")
AS 'Paul',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Dave') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Dave') > "0" AND DATEDIFF(day,"today",'Dave') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Dave') + "{cell}")
AS 'Dave'
FROM T*

Mon 1-2.png

Hello @Katerina Kovriga _Stiltsoft_  this made all my empty cells red as well. Conf.PNG

Hi @Pansilu Diegayu Wickramasinghe ,

If you have empty cells in your table and want them to be white (or any other color you wish), add an additional "when" entry into your query:

WHEN T1.'Jack' IS NULL THEN "#FFFFFF"

Here is an example for T1.'Jack' with this additional entry and T1.'Nick' without it:

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Jack' IS NULL THEN "#FFFFFF"
WHEN DATEDIFF(day,"today",'Jack') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jack') > "0" AND DATEDIFF(day,"today",'Jack') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Jack') + "{cell}")
AS 'Jack',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN DATEDIFF(day,"today",'Nick') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Nick') > "0" AND DATEDIFF(day,"today",'Nick') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + FORMATDATE(T1.'Nick') + "{cell}")
AS 'Nick'
FROM T* 

Wed 1-1.png

Hi @Katerina Kovriga _Stiltsoft_  this worked. I have one last question. I have cells with a text such as " No expiration" and I want these cells to be green. How would I write a code for this?

Conf1.PNG

Hi @Pansilu Diegayu Wickramasinghe ,

Try the following query:

SELECT *,
FORMATWIKI("{cell:bgColor=" +
CASE
WHEN T1.'Jack' IS NULL THEN "#FFFFFF"
WHEN DATEDIFF(day,"today",'Jack') <= "0" THEN "#FA7E70"
WHEN DATEDIFF(day,"today",'Jack') > "0" AND DATEDIFF(day,"today",'Jack') <= "180" THEN "#FFE784"
ELSE "#8FCA7D"
END
+ "}" + IF(T1.'Jack' > 0, FORMATDATE(T1.'Jack'), T1.'Jack') + "{cell}")
AS 'Jack'
FROM T*

0 votes
Brant Schroeder Community Leader Jan 03, 2021

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.

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 exampleimage.png

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
Community showcase
Published in Confluence

Announcing Team Calendars in Confluence Data Center

Hi Community! We're thrilled to share that Team Calendars for Confluence is now a built-in feature for Confluence Data Center releases 7.11 and beyond.  A long time favorite,  Team Cale...

156 views 0 5
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you