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; } } }

How do I calculate duration between the dates in Confluence excluding weekends

Gireesha Jeera

I am trying to display duration column with difference between 2 dates in Confluence. I have tried below:

select *,(CAST(ROUND(ceiling(DATEDIFF(DAY,'From Date','To Date')))as int))+1+ " days" as 'Duration' from T1

this gives the result, however given the dates 25-Jun-2020 to 26-Jun-2020 - duration displayed as 1 instead of 2.

your help is appreciated here. thanks.

Accepted answers

Andrey Khaneev _StiltSoft_

Hi @Gireesha Jeera, I used your query in the Table Transformer macro and it works as expected:

What version of Table Filter and Charts do you use?

Did you set the correct date format dd-M-yy in the macro settings?

All comments

Andrey Khaneev _StiltSoft_

Hi @Gireesha Jeera, I used your query in the Table Transformer macro and it works as expected:

What version of Table Filter and Charts do you use?

Did you set the correct date format dd-M-yy in the macro settings?

Gireesha Jeera

Hi @Andrey Khaneev _StiltSoft_ ,

Thanks for the quick response, in my case table looks as below:

for me this still displayed as 1 days, can you please help here ?

confluence`.jpg

Gireesha Jeera

Also @Andrey Khaneev _StiltSoft_ it works may be because i have added + 1 for the below query. however this query doesnt work if there are blank dates and displayed as 1 days.

select *,(CAST(ROUND(ceiling(DATEDIFF(DAY,'From Date','To Date')))as int))+1+ " days" as 'Duration' from T1

so try this and let me know, thank you.

select *,(CAST(ROUND(ceiling(DATEDIFF(DAY,'From Date','To Date')))as int))+ " days" as 'Duration' from T1

Andrey Khaneev _StiltSoft_

Strange... Could you try this query:

select *,('To Date' - 'From Date' + "1d") / "1d" + " days" as 'Duration' from T1

Godwin jino

@Gireesha Jeera

i have used this SQL

SELECT *, 'Days' - 2 * (('Days' / 7)::integer) - CASE WHEN 'Days' % 7 = 0 THEN 0 WHEN 'Start date'::Date->getDay() = 0 THEN 1 WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1 WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2 ELSE 0 END AS 'Work days' FROM (SELECT *, ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days' FROM T1)

And next step i don't have clear idea can you help to what are the fields needs to be updated .

Stiltsoft support

Hi @Godwin jino ,

Please check the new example from our documentation:

https://docs.stiltsoft.com/tfac/cloud/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Calculatingthenumberofworkdaysinaperiodoftime

Wed 13-1.png

SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN 'Start date'::Date->getDay() = 0 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)

Wed 13-2.png

If smth goes wrong for you, please refer to our support. Attach the page storage of your page (upper right corner -> menu ... -> View storage format). Then we'll be able to recreate exactly your page and look into the issue.

Godwin jino

@Stiltsoft support ,

Thanks for your answer its working now.

Welcome!

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

Quick Links

Find more posts tagged with

confluence

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets