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

[gurusai sankar]

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

 

DATEDIFF("day",[sTART WEEK],[END WEEK])

- (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

0 ELSE 1 END)

 

Thanks in Advance,

Guru Sai sankar.

[Thom Siragusa]

Please seehttps://community.spotfire.com/questions/i-need-calculate-number-working-da...

[gurusai sankar]

Thanks Thom for your prompt reply and this is not working for my scenario. Actually i want to calculate working days (5per week) including my start date and end date. my start date is on saturday and end date is on friday. 

[Thom Siragusa]

Hi Guru, please send some examples of input data and desired result. I need to understand better what the rules are. 

thanks

[Manoj Singh]

You can have a look into the below articles:https://support.tibco.com/s/article/How-to-calculate-working-days-between-two-dates-in-Spotfire

 

https://www.bigmountainanalytics.com/calculate-working-days-between-two-dates-in-spotfire-and-alteryx/

[gurusai sankar]

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. 

 

 

 

Regards,

 

Guru. 

[James Watts]

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).

[gurusai sankar]

Thanks Jim for your prompt reply. I wil try to implement the same way and update.