-- get the work days between the mondays follwoing each date
abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ), (dateadd(dd,1,dateceiling(wk,dt2)) ))) - round(abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ) , (dateadd(dd,1,dateceiling(wk,dt2)) )))/7,0)*2 +
--add on the working days to the following monday for the first date
case when (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else cast(dateformat(dt1,'d') as numeric)-1 end ) < 0
then 0 else (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else cast(dateformat(dt1,'d') as numeric)-1 end ) end -
--take off the working days to the follwoing monday for the second date
case when (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else cast(dateformat(dt2,'d') as numeric)-1 end) < 0
then 0 else (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else cast(dateformat(dt2,'d') as numeric)-1 end) end biz_days_between
Replace dt1 and dt2 with your dates. Make dt1 < dt2 otherwise I think it will break. I didn't bother testing because coding this took away my will to live.
No comments:
Post a Comment