nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Can I calculate the time elapsed between two date macros?

Matthias_d4typc

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

Accepted answers

Katerina Rudkovskaya _Stiltsoft_

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

All comments

Katerina Rudkovskaya _Stiltsoft_

Hello Matthias,

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

Best regards,
Katerina
StiltSoft

Matthias_d4typc

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.PNG 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_d4typc

Excellent Katerina, all works fine now.

Thank you and stay safe!

Matthias

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

macro