As I haven't Excel available currently, I can only describe what I would do.
I'd have a Calendary table in separate [hidden] sheet, something like:
Date, ..., WeekdayNo, IsHoliday, DaysAfterPreviousWorkday, DaysBeforeNextWorkday, ...;
All values in this table would be calculated for period determined in column Date. This column contains a list of all dates from start of calendary until end of it;
In column WeekdayNo, the weekday number for Date is calculated (values 1 - 7);
IsHoliday will be either 1 when Date is State Holiday, or 0 when otherwise;
Those 2 columns are used to calculate:
a) DaysAfterPreviousWorkday will be 0 when Date is workday. It will be > 0, when the Date is not workday. When the previous day was workday, it will be 1, when the previous day also wasn't workday, but day before it was, it will be 2, etc.;
b) DaysBeforeNextWorkday will also be 0 when Date is workday. It will be > 0, when the Date is not workday. When the next day will be workday, it will be 1, when the next day also isn't workday, but day after that it will be, it will be 2, etc.;
Now, whenever you calculate a date, and need to ensure that it will be workday, you have to read the value of DaysAfterPreviousWorkday or DaysBeforeNextWorkday from Calendary table, and substract or add this to date. When the date was workday, it remains same. Otherwise the date will be changed for proper number of days. The best formula to do this all will be SUMIFS.
|