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

Pam Larson March 11, 2019

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

3 votes
Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 9, 2022

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.

2 votes
Natalie Paramonova _Stiltsoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 19, 2019

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.

Gireesha Jeera September 24, 2020

@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

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 24, 2020

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
Gireesha Jeera September 24, 2020

@Natalie Paramonova _Stiltsoft_ 

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

 

Regards,

Gireesh

Gireesha Jeera September 25, 2020

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

Katerina Kovriga {Stiltsoft}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 25, 2020

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
Gireesha Jeera September 25, 2020

Thanks @Katerina Kovriga {Stiltsoft} That worked perfectly. thank you once again.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events