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

Duration calculation between two dates exclude weekend in confluence?

Godwin jino

Hi all,

i have created a table macro to calculate number of days between two dates (start date to end date). below i have mentioned the query . could you help me to modify to exclude weekends?

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

table macro.png

Thanks

Accepted answers

Shawn Doyle - ReleaseTEAM

You could take the number of days, divide by 7 to get weeks, then multiply by 2 to get the weekends, and then subtract that value from the number of days. Should get you fairly close.

Some SQL examples can be found here: https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

All comments

Shawn Doyle - ReleaseTEAM

You could take the number of days, divide by 7 to get weeks, then multiply by 2 to get the weekends, and then subtract that value from the number of days. Should get you fairly close.

Some SQL examples can be found here: https://stackoverflow.com/questions/7388420/get-datediff-excluding-weekends-using-sql-server

Godwin jino

 hi Doyle , i have updated my query, what are the filed to be modified to complete this table in settings?

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)

Godwin jino

hi @Shawn Doyle - ReleaseTEAM ,

i have tired above code 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

unknown-hosting-type