Excel Nested Lambda

Question

I've written the outside of a function as an Excel Lambda function.

LAMBDA(Years, TextFmt, EndOfMonth, ByRows,
    LET(
        MonthNbrs, IF(ByRows, SEQUENCE(1, 12, 1, 1), SEQUENCE(12, 1, 1, 1)),
        Dates, MAP(
            Years,
            LAMBDA(Year, MAP(MonthNbrs, LAMBDA(Month, DATE(Year, Month, 1))))
        ),
        Dates
    )
)

I'm trying to enumerate years by months.

Dates as a return keeps giving me a Calc error.

An example or two with expected results would be useful. Also, you might also explain why two of the parameters you define (TextFmt and EndOfMonth) are not used anywhere within the calculation. — Jos Woolley, Apr 11 '22 at 04:02
Yes, TextFmt and EndOfMonth are for the date format and whether you want 1st of last day of month. Reserved for future. My expected result for ({2020,2021}, "mm/dd/yyyy", FALSE, TRUE) would be the first of every month date array for the years 2020 and 2021. — ExcelIsFun_Sometimes, Apr 11 '22 at 04:15
I confess that I find your construction difficult to understand. I would have preferred something like `=LAMBDA(Years,TextFmt,EndOfMonth,ByRows,LET(Dates,TOCOL(EOMONTH(DATE(Years,SEQUENCE(12),1),IF(EndOfMonth,0,-1))+1-EndOfMonth),IF(ByRows,Dates,TRANSPOSE(Dates))))`. If you don't have access to `TOCOL` this can be replaced with an alternative. — Jos Woolley, Apr 11 '22 at 05:01
Thank you kind Sir. I do have TOCOL and it works. In my original suggestion, I was trying to do a nested loop with the MAP function to achieve By Year and then By Months. I still will try to figure out TOCOL for people who aren't on the beta version. Best to you. — ExcelIsFun_Sometimes, Apr 11 '22 at 18:06

score 0 · Answer 1 · edited Apr 15 '22 at 22:12

0

Jos Wooley nailed it for people who have the TOCOL function.

=LAMBDA(Years,TextFmt,EndOfMonth,ByRows,LET(Dates,TOCOL(EOMONTH(DATE(Years,SEQUENCE(12),1),IF(EndOfMonth,0,-1))+1-EndOfMonth),IF(ByRows,Dates,TRANSPOSE(Dates))))

edited Apr 15 '22 at 22:12

ZygD

22,092
39
79
102

answered Apr 11 '22 at 18:08

ExcelIsFun_Sometimes

45
6

Excel Nested Lambda

1 Answers1