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

Can I calculate the time elapsed between two date macros?

Hello, I am currently setting up a page property report for an overview page that tracks individual jobs our team is working on. The columns are client, assignee, begin date + end date (as macros) etc. 

Our team leader would now like to track the individual time any job lasts in a seperate column entitled "duration". In order to avoid manual calculation I would like to automate this task by calculating the delta between the begin date macro and the end date macro.

The result should show up as "5 days" or similar in a seperate column. 

NB our jobs typically run uninterrupted for several days also over weekends, so there is no need to differentiate between business days and days off.

Thank you!

Matthias

1 answer

Hello Matthias,

This use case can be resolved with the help of the Table Filter and Charts for Confluence app.
You can wrap your Pape Properties Report with the Table Transformer macro and enter the following SQL query:
SELECT *, DATEDIFF(day,'Begin date','End date')+ " days" as 'Duration' from T1

1.png4.png

5.png

Please find our documentation to get more info how to use our app.

Best regards,
Katerina
StiltSoft

Hello Katerina,

thank you so much for your answer, it worked out just fine. There are only two minor issues, hope you can help me with these as well:

1) The "Duration" now shows up in in decimal number days (14,9999999 days) instead of rounded integers (15 days) - can you tell me where to change this?

2) I also have Table Filter included to make it easier browse the files, my current page architecture looks like this:

2020-04 Confluence msr.PNGProblem is that the report filter to help browse the page now appears BELOW the list, the team would prefer to have it on top of the list (specifically once we have several hundred entries it would be very clumsy to scroll all the way down to the list to start a filter search.

Maybe you can help there as well. Other than that, thanks for the quick reply here!

Matthias 

Hello Matthias,

Let's try to replace that SQL query for the following:
SELECT *, ROUND(DATEDIFF(day,'Begin date','End date'))+ " days" as 'Duration' from T1

6.png


Regarding the second question, you need to wrap the Page Properties Report macro with Table Transformer at first and then add Table Filter.

Best regards,
Katerina
StiltSoft

Excellent Katerina, all works fine now.

Thank you and stay safe!

Matthias

You're welcome, Matthias :)
Thanks, you too! 

Katerina 
StiltSoft

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