It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Duration calculation between two dates in confluence?

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?

1 answer

Hi Bernett,

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

Currently looks like this:

Image 2019-06-12 at 9.35.10 AM.png

Here is what I have managed to do:

I created a vertical table instead of horizontal

2019-06-12_17h24_36.pngThat's because I'm not it is possible to do the trick with horizontally oriented table.

Then I added Table Transformer macro:

2019-06-12_17h26_15.png

2019-06-12_17h26_58.png2019-06-12_17h27_11.png


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:

2019-06-12_17h22_47.pngThe SQL query can be changed to show years or year when it is needed, show number of days and so on.

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

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.

Hi Andrey,

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

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*)
Like erzn likes this

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

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?

Hi Kevin,

Here is an example with two dates:

2020-05-29_15h37_14.png2020-05-29_15h37_32.png

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.

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 ? 

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*

2020-09-10_10h13_24.png

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

Suggest an answer

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

Lessons and Learnings: Six Months of Working Remote [Discussion]

Hey there, folks! For most of us, the past six months- yes, you read that right- have been a journey. More people than ever before have pivoted to working remotely, and navigating being on-scre...

8,532 views 6 6
Join discussion

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