List the 2nd Tuesday of the month for the next 12 months - Calculation Error

[Aussiebear]

I came across this file recently, whilst browsing on my phone. In trying to re create it in Excel, I'm coming up with a "Calc" error on my Mac (which suggests an empty array), but on MS system shows "#Name error", and when evaluating shows "TODAY" as underlined and "the next evaluation will result in an error" message. Can anyone assist me here please?


=Let(start, EOMONTH(TODAY(), -1)+1, months,B5,n,B11,dow,B8, end,EDATE(start, months)-1, dates,SEQUENCE(end-start +1,1, start, 1), _
dates,FILTER(dates,TEXT(dates,"dddd") = dow), instance, BYROW(dates,LAMBDA(d, SUM((TEXT(d, "mmyy") = TEXT(dates,"mmyy"))* _
(d >= dates)))), FILTER(dates,instance = n))

[June7]

The only functions showing available for me are SUM() and TEXT() and EOMONTH(). I am using Excel 2010. What version are you using?

Posted worksheet shows an array formula, your posted expression is not.
https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Review https://www.myonlinetraininghub.com/list-first-monday-date-in-each-month

Why is this workbook an xlsm when there is no macro code?

[Aussiebear]

Office 365 for Mac, That's what I was given, and in relation to XlSM, because its the start of a multiple sheeted workbook which will include code added as I go.

[Paul_Hossler]

That formula shows up here

https://exceljet.net/formulas/list-nth-weekdays-of-the-month

with a good explaination


This



FILTER(dates,TEXT(dates,"ddd")=dow)



should be this



FILTER(dates,TEXT(dates,"dddd")=dow)

[Aussiebear]

Thank you Paul. In the attached worksheet I've adjusted it to allow the User to choose the Number of Months, Select a day of the week, and the nth occurrence of the day selected (Maximum of 5). So if there's any association out there who wants to find the dates for a monthly meeting for the next couple of years..... You can Thank Dave at Exceljet.org and Paul Hossler from here.

[arnelgp]

chip pearson has made similar function long time ago.
VBA Procedures For Dates And Times (cpearson.com) (http://www.cpearson.com/excel/datetimevba.htm)

[Aussiebear]

Thank you arnelgp but I have a version which I have accepted as a solution

[arnelgp]

:thumb

[georgiboy]

Here are two shorter formulae without the use of LAMBDA, there are two options, one with text days of the week and one with numeric days of the week. The numeric days of the week formula is much shorter:

Days of the week as text:

=LET(
d,MATCH(A8,{"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"},0),
WORKDAY.INTL(EOMONTH(TODAY(),SEQUENCE(A5)-2)+1,A11,REPLACE("1111111",d,1,"0"))
)


Numeric day of the week:

=WORKDAY.INTL(EOMONTH(TODAY(),SEQUENCE(A5)-2)+1,A11,REPLACE("1111111",A8,1,"0"))

[Aussiebear]

:ack:Just when I thought it was safe to go into the water.....