Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,299,771
Community Members
 
Community Events
165
Community Groups

I need to insert a formula within a Confluence table to return a value based on two dates

I am new to Confluence.  I want to use Confluence to track goals - completion date vs. target date.  If the completion date is within five days, I want to indicate that it is "On Time" in green; if it's more than 5 days late, but less than 10 days, I want to indicate that it is "Late" in yellow; and if it's more than 10 days, I want to indicate that it is "Very Late" in red.  

Can this be done and can you help me with the formula or direct me to the information that I need to figure this out on my own?

2 answers

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

This new macro allows you to work with fully functional Excel spreadsheets right in Confluence.

You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

Hi Pam,

You can do this with the help of the Table Transformer macro (one of the macros bundled in the Table Filter and Charts app). The macro allows performing complex calculations using SQL queries.

Here is an example.

Place two tables in the macro body. The first table is your source table with dates columns. The second one is a table with colored statuses.

2019-07-19_10h39_15.png

Edit the macro and switch to the SQL query tab. Use the following query:

SELECT *, CASE WHEN (('Completion date' - 'Target date') / "24h") < 5 THEN "ON TIME" WHEN (('Completion date' - 'Target date') / "24h") > 5 AND (('Completion date' - 'Target date') / "24h") <= 10 THEN "LATE" ELSE "VERY LATE" END AS 'Rating' FROM T1

2019-07-19_10h39_50.png

Set the correct date format in the Settings tab.

2019-07-19_11h41_12.png

The result is as follows.

2019-07-19_10h38_25.png

You can use a manually created table, the Jira Issues or other native Confluence macros outputting tables as a source table.

@Natalie Paramonova _Stiltsoft_ 

i have managed to create table for similar requirement, but how color of the text changes in Rating column based on the value ?

 

can you send details on this please ?

- Gireesh

Hi @Gireesha Jeera ,

You need to have two tables wrapped in the Table Transformer macro:

  1. The table with date columns
  2. The small table with the required ratings

The second table is created with the help of the standard Status macro. It is often used to indicate the result or progress of different projects. When you create a status, you specify its name and the color of the lozenge.

Now if you go back to the SQL query, you may notice that we have a condition when to use this or that status based on the difference between the two dates from the first table and the name of the particular status:

SELECT *, CASE WHEN (('Completion date' - 'Target date') / "24h") < 5 THEN "ON TIMEWHEN (('Completion date' - 'Target date') / "24h") > 5 AND (('Completion date' - 'Target date') / "24h") <= 10 THEN "LATE" ELSE "VERY LATE" END AS 'Rating' FROM T1

As a result, we add an extra colored Ratings column to the first table based on the query.

Hope I've cleared this moment for you.

Like # people like this

@Natalie Paramonova _Stiltsoft_ 

Thanks a lot, it really helped. appreciate quick help.

 

Regards,

Gireesh

morning @Katerina Kovriga _Stiltsoft_  ,

what if i want to compare with the current date with the Target date ? how to take current date for the same query above.

Regards,

Gireesh

Hi @Gireesha Jeera

Use the "today" function:

SELECT *, CASE WHEN ("today" - 'Target date') / "24h" < 5 THEN "ON TIME"
WHEN ("today" - 'Target date') / "24h" > 5 AND
("today" - 'Target date') / "24h" <= 10 THEN "LATE"
ELSE "VERY LATE" END AS 'Rating' FROM T1

Like # people like this

Thanks @Katerina Kovriga _Stiltsoft_ That worked perfectly. thank you once again.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

316 views 2 7
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