LAMBDA: CreateEndDatesλ

Code

This is the code as of this writing. This is a good example of how to use LET and SWITCH. For the most current version, see the Gist.

/*

FUNCTION NAME: CreateEndDatesλ

DESCRIPTION: Creates a horizontal list of End dates

EXAMPLE: =CreateEndDatesλ(TODAY(), TODAY()+365, "Q")

For an Example see: https://www.dropbox.com/s/ddthth8dy9l9ug4/BudgetPQL.xlsx?dl=1

ARGS:

CalendarStart First date in calendar (and first date in list)

CalendarEnd Last date in calendar (See CreatEndDatesλ

Interval Determine the period between dates

Y=Years

Q=Quarters

M=Months

W=Weeks

D=Days

Copyright: Craig Hatmaker 2022 - Anyone may use but not claim ownership or authorship

*/

CreateEndDatesλ =LAMBDA(CalendarStart, [CalendarEnd], [Interval],

LET(

// Period in Months

SemiAnnually, 6,

Quarterly, 3,

// Period in Days

Biweekly, 14,

Weekly, 7,

// Months/Year, Months/Quarter, Days/Week

MPY, 12,

MPQ, 3,

DPW, 7,

// Define Periods

End, IF(CalendarEnd = "", EOMONTH(CalendarStart, MPY), CalendarEnd+1),

Days, End - CalendarStart,

Years, YEAR(End) - YEAR(CalendarStart),

Months, Years * MPY + MONTH(End) - MONTH(CalendarStart),

Quarters, INT(Months / MPQ),

Weeks, INT(Days / DPW),

// Function starts here

SWITCH(IF(ISOMITTED(Interval),"D",LEFT(Interval, 1)),

"Y", EDATE(CalendarStart, SEQUENCE(1, Years, MPY, MPY)) - 1,

"Q", EDATE(CalendarStart, SEQUENCE(1, Quarters, MPQ, MPQ)) - 1,

"M", EDATE(CalendarStart, SEQUENCE(1, Months, 1, 1)) - 1,

"W", SEQUENCE(1, Weeks, CalendarStart + DPW - 1, DPW),

"D", SEQUENCE(1, Days, CalendarStart, 1)

)

)

);