Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Can I have a specific word (in a cell) generate a formula to another cell?

Copper Contributor

So I want to make due dates for visits but unsure if the way I am thinking about is even possible. 

 

I would want the words "weekly," "bimonthly," "Monthly," and "quarterly," to generate the formula that would calculate the next visit date, so I can have all my information on one sheet. For example, I want the word, weekly (B2) to generate the formula in E2 (=C+7) based on the date of C2 or the word quarterly (A7) to generate the formula in E7 (=edateE7,3) based on C7.

Claudy_Maple_0-1706738155453.png

 

3 Replies

@Claudy_Maple 

=IFS(B2="Weekly", C2+7, B2="Bimonthly", C2+14, B2="Monthly", EDATE(C2, 1), B2="Quarterly", EDATE(C3, 3), B2="Biyearly", EDATE(C2, 6), B2="Yearly", EDATE(C2, 12), TRUE, "")

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

@SnowMan55 

Thanks for pointing out my typo, I will correct it.