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

[Pam Larson]

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?

[Katerina Kovriga {Stiltsoft}]

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.

[Natalie Paramonova [Stiltsoft]]

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.

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

Set the correct date format in the Settings tab.

The result is as follows.

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

[Gireesha Jeera]

@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}]

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

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.

[Gireesha Jeera]

@Natalie Paramonova [Stiltsoft] 

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

Regards,

Gireesh

[Gireesha Jeera]

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}]

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

[Gireesha Jeera]

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