Forum Discussion
Show regular days per month eg 2nd Sunday of every month
Snidely_Whiplash Here is a LAMBDA function that will do what you want (I think):
=LAMBDA(months,dayOfWeek,NthWk, LET(
mm,EOMONTH(TODAY(),months-1),
do, XMATCH(dayOfWeek, {"Mon","Tues","Wed","Thur","Fri","Sat","Sun"})+10,
mm+1-WEEKDAY(mm, do)+7*NthWk))
(1,"tues",2)
In the above example it was used inside a cell but you can save the LAMBDA function into the Name manager and then use it anywhere using the name you give it. The last line is where I passed it the parameters of 1 month from now, "Tues" being the day of the week and 2 being the 2nd Tuesday of the month. Obviously this LAMBDA needs specifically "Mon", "Tues", .... etc but you can change that to what ever you want to use.
BTW if you save the LAMBDA portion (lines 1-4) in the name manager and let's say you call it FutureDate then you can call it from any cell using =FutureDate(1,"tues",2)
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