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.

[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

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)

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.