Calculating Day Weights for business days

Question

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.

DateType of DayDay of WeekDay Factor3/30/2020Business DayMonday13/31/2020Business DayTuesday14/1/2020Business DayWednesday14/2/2020Business DayThursday14/3/2020Business DayFriday34/4/2020WeekendSaturday04/5/2020WeekendSunday04/6/2020Business DayMonday14/7/2020Business DayTuesday14/8/2020Business DayWednesday14/9/2020Business DayThursday44/10/2020HolidayFriday04/11/2020WeekendSaturday04/12/2020WeekendSunday04/13/2020Business DayMonday1

AbhilashR · Accepted Answer

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 Answer

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.