Forum Discussion
Show regular days per month eg 2nd Sunday of every month
I am involved in multiple market events and want to create a calendar schedule which automatically shows the next event date for each market and then a formula which calculates the following event da...
thank you mtarler, I have got this working in my sheet, much appreciated. So currently, as the Next Date passes, I will need to go in and update each formula for the parameters to reflect the next month. To make it future proof, is there a way to automate the change of month once the Next Date is passed, or do I need to list all future dates for each event? Thank you for your help.
Snidely_Whiplash In that attached I demonstrate how it can be a function in the Name Manager. I also updated it to have an input for the starting date and demonstrate how you can even pass an array to get multiple dates out. Updated LAMBDA function:
NextDate
=LAMBDA(startDate,months,dayOfWeek,NthWk, LET(
mm,EOMONTH(startDate,months-1),
do, XMATCH(dayOfWeek, {"Mon","Tues","Wed","Thur","Fri","Sat","Sun"})+10,
mm+1-WEEKDAY(mm, do)+7*NthWk))notice I pass TODAY() for the starting date. I also pass {1,2,3} to get out the next 3 months of dates as you see spilling in cells B1, C1, and D1