Jump to content

How to calculate working days based on start week and end week (including start week and end week)

gurusai sankar

Recommended Posts

Hi All,

i have the data in week wise (MM:DD:YY) for 2019, 2020 (start week and end week). My Start week stats with Saturday and End week ends with Friday.

I need to caluclate working days (5 per week) based on the start date and end date (including start date and end date)

i am using below caluclation, its working fine with in the year i.e start week and week both in the same year , but when start week is 2019 and end week is 2020 (ex: 12/28/2019 - 1/3/2020) - its not giving the right out put for this particular week (it is showing 109 instead of showing 5 working days per week including start week and end week)

PFA- attached sample dxp. i am expecting the 5 working days insted of 109 (12/28/19 - 1/3/2020) in my dxp

Can some one help on this issue else how can i modify this expression according to my requirement



- (2 * (DATEPART("week",[END WEEK]) - DATEPART("week",[sTART WEEK])))

+ (CASE WHEN (DayOfWeek([END WEEK])=6) AND (DayOfWeek([sTART WEEK])=0) THEN -1

WHEN (DayOfWeek([END WEEK])=6) OR (DayOfWeek([sTART WEEK])=0) THEN



Thanks in Advance,

Guru Sai sankar.

Link to comment
Share on other sites

Hi Manoj, Thanks for your reply. I also followed the same logic as per 2nd link whch you provided. But here problem is - when my start date is 12/28/19 and end date 1/3/2020- It is showiing my working days as 109 insted of showing 5 (rest all other weeks are showing as 5 working days including my start date and end date which is correct) , it seems problem comes when my start date and end date have different years. 


If possble- could you please go thorogh my dxp and look at that week (12/28/19 - 1/3/2020) why it showing 109 working days instead of showing 5 . 


Please help on this. 







Link to comment
Share on other sites

The problem is that there are not an exact number of weeks in the year - the above calc will fail because of the extra days in week 53 of 2019.  The calc needs to be based on the actual number of days between the dates, then calculate how many weekend days that includes.  (This way should also take account of leap years).
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...