Calculating Day Weights for business days

[jdejesus]

Hello,

I am looking to calculate the "Day Factor" column below. If i have a date range, i want to assign Business Days as 1 and non business days as 0. This portion is already predefined. The question i have, is that if a Business day is followed by a non business day, i want to assign the weekend days to the last business day before the non business day. Hence, below for 4/3/20, since it is a Friday, its day factor is 3 (it's counting Friday, Saturday and Sunday). For 4/9/20, its day factor is 4 (counting Thursday, Friday, Saturday, Sunday) since Friday is a Holiday.

Date	Type of Day	Day of Week	Day Factor
3/30/2020	Business Day	Monday	1
3/31/2020	Business Day	Tuesday	1
4/1/2020	Business Day	Wednesday	1
4/2/2020	Business Day	Thursday	1
4/3/2020	Business Day	Friday	3
4/4/2020	Weekend	Saturday	0
4/5/2020	Weekend	Sunday	0
4/6/2020	Business Day	Monday	1
4/7/2020	Business Day	Tuesday	1
4/8/2020	Business Day	Wednesday	1
4/9/2020	Business Day	Thursday	4
4/10/2020	Holiday	Friday	0
4/11/2020	Weekend	Saturday	0
4/12/2020	Weekend	Sunday	0
4/13/2020	Business Day	Monday	1

[AbhilashR · Accepted solution]

Hi @jdejesus, assuming your data already has Type of Day column the attached solution might help you achieve your end goal.

Let us know if this isn't what you are looking for.

[pankajk · Accepted solution]

Hi @jdejesus  - here is my solution....... give a value to "Business day", map non-business days back to last business day and do a count and join back.

[jdejesus]

Thank you both! both work for me.