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?

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.