@Claudy_Maple
- As you likely noticed, Hans' formula is almost correct, but it references cells C3 and V2 where it should be referencing B2.
- I recommend the use of the term Biweekly to represent 14 days; Bimonthly more often represents two months. And in the attached workbook I use the term Semiannually instead of Biyearly.
Note that the formulas in the attached workbook require Excel 2021 or a later version. The LET function allows you to write formulas that are easier to read and understand, and often can be more efficient than the alternatives.
If your schedule accepts every day of each year as a schedulable day, Hans' corrected formula works. But many institutions have just a five-day workweek, typically with Saturdays and Sundays off. To avoid scheduling on Saturdays and Sundays, you can use this formula instead:
=LET( initial_date, IFS(B2="Weekly", C2+7, B2="Biweekly", C2+14, B2="Monthly", EDATE(C2, 1), B2="Quarterly", EDATE(C2, 3), B2="Semiannually", EDATE(C2, 6), B2="Yearly", EDATE(C2, 12), TRUE, ""),
adj_for_weekends, initial_date + CHOOSE( WEEKDAY(initial_date), 1,0,0,0,0,0,2 ),
IFERROR(adj_for_weekends, "")
)
(Spaces and line breaks are not required but are included for readability.) This CHOOSE function uses the calculated day-of-week value as an index into the list of numbers that follow it.
(While my non-zero adjustments are both forward one or more days, you could alternatively include backward adjustments, e.g., backing up from an initially-calculated date on Saturday to its preceding Friday by specifying -1 instead of 2 as the seventh entry in that list.)
And if you want to avoid scheduling on holidays, that can be done also, with a modified formula and additional work. An excellent way of storing holiday information is in an Excel table.
I created the Excel table tblHolidays on the Lookup Tables worksheet. (I have just one Excel table on that worksheet, but more can be placed there as desired.) Formulas can reference Excel table data by the table name and the names of the relevant table columns.
Note: You should not type the day of the week when creating or editing entries in tblHolidays. The date values there display the day of the week because of a custom format that I applied to their columns: ddd, m/d/yyyy;@
So my formula that avoids both weekends and holidays is:
=LET( initial_date, IFS(B2="Weekly", C2+7, B2="Biweekly", C2+14, B2="Monthly", EDATE(C2, 1), B2="Quarterly", EDATE(C2, 3), B2="Semiannually", EDATE(C2, 6), B2="Yearly", EDATE(C2, 12), TRUE, ""),
adj_for_weekends, initial_date + CHOOSE( WEEKDAY(initial_date), 1,0,0,0,0,0,2 ),
adj_for_holidays, XLOOKUP(adj_for_weekends, tblHolidays[Date], tblHolidays[Reschedule Date], adj_for_weekends, 0, 1),
IFERROR(adj_for_holidays, "")
)
As written, this formula does not require the table entries to be in chronological order. But you probably want to keep the entries in chronological order. As the years pass and this table becomes large, performance will slowly degrade; specifying a binary search mode in the XLOOKUP function will minimize that degradation.
