Can I calculate the time elapsed between two date macros?

[Matthias]

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

[Katerina Rudkovskaya _Stiltsoft_ · Accepted solution]

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

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

Best regards,
Katerina
StiltSoft

[Matthias]

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:

Problem 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 

[Katerina Rudkovskaya _Stiltsoft_]

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

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

[Matthias]

Excellent Katerina, all works fine now.

Thank you and stay safe!

Matthias

[Katerina Rudkovskaya _Stiltsoft_]

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

Katerina 
StiltSoft