Forum Discussion

Snidely_Whiplash's avatar
Snidely_Whiplash
Copper Contributor
Mar 10, 2023

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 dates based on frequency eg 2nd Sunday of every month, 3rd Thursday of every month. I have tried various formula functions from similar queries in this forum but can't get them to reflect following events past current month. I started with key data as: A1 = Day of week eg Sunday, A2 = frequency eg monthly, A3 = schedule eg 3rd Sunday of month, A4 = next scheduled event eg 19 March

Thank you

 

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Mar 10, 2023

    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's avatar
      Snidely_Whiplash
      Copper Contributor
      Mar 11, 2023
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Mar 11, 2023

        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

         

Resources