Duration calculation between two dates in confluence?

[Barnett Williams]

We have a team "home page" that includes the start date of each team member. For example: April 03, 2018. 

Below the start date, I am trying to calculate the running year/month tenure of each team member. Something simple like: =(TODAY DATE - 2019-04-03) / 12. Ideally, this would then be calculated to a year, month format similar to LinkedIn's 1yr 4mos. 

Is this possible in confluence?

[Andrey Khaneev _StiltSoft_ · Accepted solution]

Hi Bernett,

Is this data stored in a table format? If yes, then the Table Transformer can do the trick.

[Barnett Williams]

Currently looks like this:

[Andrey Khaneev _StiltSoft_]

Here is what I have managed to do:

I created a vertical table instead of horizontal

That's because I'm not it is possible to do the trick with horizontally oriented table.

Then I added Table Transformer macro:

SQL code:

SELECT 'Teammate', 'Position', 'Location', 'Anniversary Date',
CASE
WHEN 'Years' > 0 THEN 'Years' + " year(s) "
ELSE " "
END +
CASE
WHEN 'Months' > 0 THEN 'Months' + " month(s)"
ELSE " "
END AS 'Standing'
FROM
(SELECT *, 
YEAR("today" - 'Anniversary Date') - 1970 AS 'Years',
MONTH("today" - 'Anniversary Date') - 1 AS 'Months'
FROM T*)

And got this result:

The SQL query can be changed to show years or year when it is needed, show number of days and so on.

[Barnett Williams]

Thank you for this in-depth tutorial! For whatever reason the {table transformer} macro is not showing for me?

[Andrey Khaneev _StiltSoft_]

Yes, sorry. This macro comes from the Table Filter and Charts app.

And one more thing, I see that dates format differs in your example: June 18th, 2018 / Dec 4th, 2017 / April 3, 2018.  You'll need to use the same date format in all cells, or some dates won't be processed correctly.

[erzn]

Hi Andrey,

thank you for this great documentation. is it possible to extend your sql-code by days? best regards

[Andrey Khaneev _StiltSoft_]

Hi Ersin,

Yes, it is possible:

SELECT 'Teammate', 'Position', 'Location', 'Anniversary Date',
CASE
WHEN 'Years' > 0 THEN 'Years' + " year(s) "
ELSE " "
END +
CASE
WHEN 'Months' > 0 THEN 'Months' + " month(s) "
ELSE " "
END +
CASE
WHEN 'Days' > 0 THEN 'Days' + " day(s) "
ELSE " "
END AS 'Standing'
FROM
(SELECT *, 
YEAR("today" - 'Anniversary Date') - 1970 AS 'Years',
MONTH("today" - 'Anniversary Date') - 1 AS 'Months',
DAY("today" - 'Anniversary Date') AS 'Days'
FROM T*)

[erzn]

Thank you for your quick response. it works great as it should do :) 

[Kevin Hunter]

I tripped on this today and it works wonderfully, but crashing and burning on attempting to do *two* of these on each row...  I have a list of team members and I want to calculate both the time since they started with our company, and the time in their current role. Is it possible to do both calculations on the same row?

[Andrey Khaneev _StiltSoft_]

Hi Kevin,

Here is an example with two dates:

SELECT 'Anniversary Date', 'Other date',
CASE
WHEN 'Years' > 0 THEN 'Years' + " year(s) "
ELSE " "
END +
CASE
WHEN 'Months' > 0 THEN 'Months' + " month(s)"
ELSE " "
END AS 'Standing',
CASE
WHEN 'Years od' > 0 THEN 'Years od' + " year(s) "
ELSE " "
END +
CASE
WHEN 'Months od' > 0 THEN 'Months od' + " month(s)"
ELSE " "
END AS 'Standing Other'
FROM
(SELECT *, 
YEAR("today" - 'Anniversary Date') - 1970 AS 'Years',
MONTH("today" - 'Anniversary Date') - 1 AS 'Months', 
YEAR("today" - 'Other date') - 1970 AS 'Years od',
MONTH("today" - 'Other date') - 1 AS 'Months od'
FROM T1)

The additional query is a copy paste of the original. Also it can be much simplified if it is fine to see 0 years and 0 months.

[jason sheridan]

Hi @Andrey Khaneev _StiltSoft_ - your solution is awesome.  What has hamstrung me with confluence many times, is the ability to dynamically calculate date.

If you are still looking at this, I'd be interested in a couple of variations:

1.  The calcs split out the years/months/days.  Is it possible to just show one time type - ie. I am only interested in seeing the result in total number of days rather than the years and months as well (ie 150 days)

2. Have you ever managed to calculate a date in one row - against a date in another row ? 

[Andrey Khaneev _StiltSoft_]

Hi @jason sheridan,

Glad to know you found this solution useful.

1. It is even easier than the original solution:

SELECT *,
(T1.'End Date' - T1.'Start Date') / "24h" AS 'Days',
("today" - T1.'Start Date') / "24h" AS 'Days till today'
FROM T*

2. Most likely it is possible, Could attach a screenshot of the table and describe what should be calculated?

Also, you can find some useful solutions here.