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.

[Andrey Khaneev _StiltSoft_ · Accepted solution]

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 ?

[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

[Andrey Khaneev _StiltSoft_]

Your second query outputs "1 days"  for the same dates.

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

[Gireesha Jeera]

@Andrey Khaneev _StiltSoft_ 

this query gives output of 4.428571428571429 days in Duration column. instead of day in whole number format (should be 2 days) thank you.

[Andrey Khaneev _StiltSoft_]

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

Please, try this. By default "1d" equals to 8 hours (working day). You can change it in the Worklog settings:

[Gireesha Jeera]

@Andrey Khaneev _StiltSoft_ Perfect works mate, thank you.

is there any solution to exclude weekends while calculating the number of days between the dates using SQL query ?

Thank you once again mate :)

[Andrey Khaneev _StiltSoft_]

Glad to help you!

Yes, it is possible, but the query is quite complex. See the docs. Let me know if you have any questions.

[Gireesha Jeera]

@Andrey Khaneev _StiltSoft_ perfect, that works perfectly. thank you once again.

[Gireesha Jeera]

@Andrey Khaneev _StiltSoft_ sorry to trouble you again,

i have tried between the dates 19 Oct 2020 to 30 Oct 2020, its results in 12.041666666666666 days

can you help here to round off please ?

i am using below query currently:

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

[Andrey Khaneev _StiltSoft_]

@Gireesha Jeera, that's because of one extra hour due to daylight saving time. Please, ROUND the calculated value:

select *,ROUND(('To Date' - 'From Date' + "24h") / "24h") + " days" as 'Duration' from T1

[Gireesha Jeera]

Thanks @Andrey Khaneev _StiltSoft_  it worked for the number of days but for empty values of both from date and date displayed as "NaN days"

[Andrey Khaneev _StiltSoft_]

Please, use this query:

select *,
CASE WHEN T1.'To Date' IS NOT NULL AND T1.'From Date' IS NOT NULL THEN
ROUND(('To Date' - 'From Date' + "24h") / "24h") + " days"
ELSE "" END
as 'Duration' from T1

[Gireesha Jeera]

@Andrey Khaneev _StiltSoft_ thanks a lot mate, it worked perfectly.

thank you once again.