Skip to content

Instantly share code, notes, and snippets.

@CHatmaker

CHatmaker/BXL 5g Functions LAMBDA for Excel Dates.txt

Last active March 11, 2025 16:52
Show Gist options
  • Save CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b to your computer and use it in GitHub Desktop.
Save CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b to your computer and use it in GitHub Desktop.
Download ZIP
BXL 5g Functions LAMBDA for Excel Dates
Raw
BXL 5g Functions LAMBDA for Excel Dates.txt
/* Module Contains 5g Compliant functions that deal with dates */
/* FUNCTION NAME: Aboutλ
DESCRIPTION:*//**Displays the URL to this module's Gist which includes documentation*/
/* REVISIONS: Date Developer Description
Mar 17 2023 Craig Hatmaker Original Development
Mar 22 2023 Craig Hatmaker Added About
Apr 06 2023 Craig Hatmaker Added Help to LAMBDAs
Aug 28 2023 Craig Hatmaker Conformed to new template
Jan 02 2024 Craig Hatmaker See CountDOWλ
Jan 05 2024 Craig Hatmaker Add all data validation/diagnostic routines
Jun 06 2024 Craig Hatmaker Code Simplification
Feb 18 2025 Craig Hatmaker See Periodsλ
Mar 07 2025 Craig Hatmaker Version 2 (deprecates previous version)
*/
Aboutλ = TRIM(
TEXTSPLIT(
"About: →5g functions dealing with dates in models. Suggested module name: BXD¶" &
"Version: →Mar 07 2025¶" &
"Gist URL: →https://gist.github.com/CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b ¶" &
"Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" &
"→¶" &
"Function →Description¶" &
" Aboutλ →Produces this table¶" &
" CountDOWλ →Count instances of a specific day of the week between two dates¶" &
" CountDOWInMonthλ →Count instances of a specific day of the week in a month.¶" &
" IsBetweenλ →Determine if a value is between a lower and upper limit¶" &
" IsLeapYearλ →Determine if a date is in a leap year¶" &
" IsOccurrenceDateλ →Determine if a date passed is when a potentially repeating event happens¶" &
" OverLapDaysλ →Return how many days overlap two period ranges.¶" &
" PeriodLabelλ →Creates a label for a date based on period interval¶" &
" Periodsλ →Determine the number of periods from Starts to Ends inclusive¶" &
" ScheduleRatesλ →Schedule rates that persist until replaced in a timeline.¶" &
" ScheduleValuesλ →Schedules values in a timeline. ¶" &
" Timelineλ →Creates a horizontal list of start or end dates for a timeline ¶" &
" DeriveTimelinesλ →Create internal start and end timelines based on the model's timeline" ,
"→","¶"
)
);
/* FUNCTION NAME: CountDOWλ
DESCRIPTION:*//**Count instances of a specific day of the week between two dates.*/
/* REVISIONS: Date Developer Description
2010 Barry Houdini Original Formula
Aug 28 2023 Craig Hatmaker Original Development
Jan 01 2024 Craig Hatmaker Corrected Help
*/
CountDOWλ = LAMBDA(
// Parameter Declarations
[Starts],
[Ends],
[DayOfWeek],
// Help
LET(
Help, TRIM( TEXTSPLIT(
"FUNCTION: →CountDOWλ(Starts, Ends, DayOfWeek)¶" &
"DESCRIPTION: →Count instances of a specific day of the week between two dates.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-countdow%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" Starts →(Required) One of more start dates (text or numeric) in a row or column¶" &
" Ends →(Required) End dates (text or numeric) for each start date¶" &
" DayOfWeek →(Required) 1=Sunday, 7=Saturday.¶" & "EXAMPLES: →¶" &
"EXAMPLE: →¶" &
" Formula →=CountDOWλ(""3/22/2010"", ""4/10/2010"", 1)¶" &
" Result →2",
"→",
"¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(Starts),
ISOMITTED(Ends),
ISOMITTED(DayOfWeek)
),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtStarts, IF(ISOMITTED(Starts), FALSE, IF(ISNUMBER(Starts), Starts, DATEVALUE(Starts))),
CvtEnds, IF(ISOMITTED(Ends), FALSE, IF(ISNUMBER(Ends), Ends, DATEVALUE(Ends))),
// Procedure
Result, INT(((CvtEnds - CvtStarts) + WEEKDAY(CvtStarts - DayOfWeek)) / 7),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: CountDOWInMonthλ
DESCRIPTION:*/ /**Count instances of a specific day of the week in a month.*/
/* REVISIONS: Date Developer Description
2010 Barry Houdini Original Formula
Aug 28 2023 Craig Hatmaker Original Development
*/
CountDOWInMonthλ = LAMBDA(
// Parameter Declarations
[DateInMonth],
[DayOfWeek],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →CountDOWInMonthλ(DateInMonth, DayOfWeek)¶" &
"DESCRIPTION: →Count instances of a specific day of the week in a month.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-countdowinmonth%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" DateInMonth →(Required) Any date within the month to count¶" &
" DayOfWeek →(Required) 1=Sunday, 7=Saturday.¶" &
"→¶" &
"EXAMPLES: →¶" &
" Formula →=CountDOWInMonthλ(""1/1/2023"", 1) ¶" &
" Result →5",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED( DateInMonth),
ISOMITTED( DayOfWeek)
),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtDates, IF( ISNUMBER( DateInMonth), DateInMonth, DATEVALUE( DateInMonth)),
// Procedure
Days, DAY(EOMONTH( CvtDates, 0)) - 1,
MonthStart, EOMONTH( CvtDates, -1 ) + 1,
Result, INT( ( Days + WeekDay( MonthStart - DayOfWeek)) / 7),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: IsBetweenλ
DESCRIPTION:*//**Determine if a value is between a lower and upper limit*/
/* REVISIONS: Date Developer Description
Jun 01 2022 Craig Hatmaker Copyright
Apr 12 2023 Craig Hatmaker Added Help
*/
IsBetweenλ = LAMBDA(
// Parameter Declarations
[Values],
[Low],
[High],
[Inclusive],
// Help
LET(
Help, TRIM( TEXTSPLIT(
"FUNCTION: →IsBetweenλ(Values, Low, High, [Inclusive])¶" &
"DESCRIPTION: →Determine if a value is between a lower and upper limit.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-sample-components/5g-isbetween%CE%BB ¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" Values →(Required) one or more values to compare.¶" &
" Low →(Required) The lower limit that the value(s) must be greater than¶" &
" High →(Required) The higher limit that the value(s) must be less than¶" &
" Inclusive →(Optional) TRUE: Value(s) can be equal to Low and/or High (default)¶" &
"EXAMPLES: →¶" &
" Result →Formula¶" &
" FALSE →=IsBetweenλ(1, 2, 4)¶" &
" TRUE →=IsBetweenλ(Today(), Today(), Today()+1)¶" &
" FALSE →=IsBetweenλ(""B"", ""B"", ""D"", FALSE)",
"→", "¶"
)
),
// Check inputs - Set optional arguments defaults
Inclusive, IF(ISLOGICAL(Inclusive), Inclusive, TRUE),
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(Values),
ISOMITTED(Low),
ISOMITTED(High)
),
// Procedure
Result, IF( Inclusive,
(Values >= Low) * (Values <= High) = 1,
(Values > Low) * (Values < High) = 1
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: IsLeapYearλ
DESCRIPTION:*//**Determine if a date is in a leap year*/
/* REVISIONS: Date Developer Description
Mar 01 2025 Craig Hatmaker Initial Development. Formula: Sergei Baklan
*/
IsLeapYearλ = LAMBDA(
// Parameter Declarations
[Date],
// Help
LET(
Help, TRIM( TEXTSPLIT(
"FUNCTION: →IsLeapYearλ( Date)¶" &
"DESCRIPTION: →Determine if a date is in a leap year¶" &
"WEBSITE: →<coming soon> ¶" &
"VERSION: →BXL: Mar 01 2025¶" &
"PARAMETERS: →¶" &
" Date →(Required) Date to determine if in a leap year.¶" &
"EXAMPLES: →¶" &
" Formula →=IsLeapYearλ(""2024-01-01"")¶" &
" Result →TRUE",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( Date ),
// Check inputs - Set optional arguments defaults
Date, IF( ISTEXT( Date), DATEVALUE( Date), Date),
// Procedure
Result, MONTH( DATE( YEAR( Date), 2, 29)) = 2,
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: IsOccurrenceDateλ
DESCRIPTION:*//**Returns TRUE if Date is when something happens. This was developed for budgeting where we
have expenses that start, and sometimes repeat at regular intervals.*/
/* REVISIONS: Date Developer Description
Jun 01 2022 Craig Hatmaker Copyright
Apr 07 2023 Craig Hatmaker Added Help
*/
IsOccurrenceDateλ = LAMBDA(
// Parameter declarations
[Dates],
[FirstOccurence],
[LastOccurence],
[Repeats],
// Help
LET(
Help, TRIM( TEXTSPLIT(
"FUNCTION: →IsOccurrenceDateλ(Dates, FirstOccurence, [LastOccurence], [Repeats])¶" &
"DESCRIPTION: →Returns TRUE if a Date is when something happens.¶" &
"→This was developed for budgeting where we have expenses that start,¶" &
"→and sometimes repeat at regular intervals.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-isoccurrencedate%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" Dates →(Required) Date to test. Normally this is horizontal timeline by day¶" &
" FirstOccurence→(Required) When something that may repeat, happens for the first time.¶" &
" LastOccurence →(Optional) When something that may repeat, happens for the last time.¶" &
"→If omitted, it is assumed repeating items repeat forever.¶" &
" Repeats →(Optional) A code that conveys how often this thing repeats. Repeat codes are¶" &
"→ O=One time/never repeats (default)¶" &
"→ A=Annually¶" &
"→ S=Semi-Annually¶" &
"→ Q=Quarterly¶" &
"→ M=Monthly¶" &
"→ B=BiWeekly¶" &
"→ W=Weekly¶" &
"→ X=Every Workday (USA normal)¶" &
"→ D=Daily¶" &
"EXAMPLES: →¶" &
"Result →Formula ¶" &
" 72 →=SUM(N(IsOccurrenceDateλ(SEQUENCE(,90,""1/1/2023""), ""1/1/2023"", ""3/13/2023"", ""D"")))¶" &
" 01 →=SUM(N(IsOccurrenceDateλ(SEQUENCE(,90,""1/1/2023""), ""1/1/2023"", ""3/13/2023"", ""O"")))¶" &
" 11 →=SUM(N(IsOccurrenceDateλ(SEQUENCE(,90,""1/1/2023""), ""1/1/2023"", ""3/13/2023"", ""W"")))",
"→","¶"
)
),
// Named constants
SemiAnnually, 6, //Months
Quarterly, 3, //Months
Biweekly, 14, //Days
Weekly, 7, //Days
Mon2Sun, 2, //WEEKDAY() Type parameter
Friday, 5, //WEEKDAY()'s value for Friday when type= 2
// Check inputs - Omitted required arguments
Help?, OR(
ISOMITTED(Dates),
ISOMITTED(FirstOccurence)
),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtDates, IF(ISNUMBER(Dates), Dates, DATEVALUE(Dates)),
CvtFirsts, IF(
ISOMITTED(FirstOccurence),
FALSE,
IF(ISNUMBER(FirstOccurence), FirstOccurence, DATEVALUE(FirstOccurence))
),
CvtLasts, IF(
ISOMITTED(LastOccurence),
FALSE,
IF(
OR(ISNUMBER(LastOccurence), TRIM(LastOccurence) = ""),
LastOccurence,
DATEVALUE(LastOccurence)
)
),
Repeats, IF(OR(ISOMITTED(Repeats), TRIM(Repeats) = ""), "O", LEFT(Repeats, 1)),
// Procedure
SameDay, DAY(CvtDates) = DAY(CvtFirsts),
Test, (CvtFirsts <= CvtDates) * IF(TRIM(CvtLasts) = "", 1, CvtDates <= CvtLasts) *
SWITCH(
Repeats,
"O", CvtDates = CvtFirsts,
"A", TEXT(CvtDates, "MMDD") = TEXT(CvtFirsts, "MMDD"),
"S", SameDay *
(MOD(MONTH(CvtDates), SemiAnnually) = MOD(MONTH(CvtFirsts), SemiAnnually)),
"Q", SameDay * (MOD(MONTH(CvtDates), Quarterly) = MOD(MONTH(CvtFirsts), Quarterly)),
"M", SameDay,
"B", MOD(CvtDates, Biweekly) = MOD(CvtFirsts, Biweekly),
"W", MOD(CvtDates, Weekly) = MOD(CvtFirsts, Weekly),
"X", WEEKDAY(CvtDates, Mon2Sun) <= Friday,
"D", TRUE,
0
),
Result, Test = 1,
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: OverLapDaysλ
DESCRIPTION:*//**Return how many days overlap two period ranges. */
/* REVISIONS: Date Developer Description
Jun 01 2022 Craig Hatmaker Copyright
Apr 12 2023 Craig Hatmaker Added Help
Jan 04 2024 Craig Hatmaker Fixed data validation bug
*/
OverLapDaysλ = LAMBDA(
// Parameter Declarations
[Period1Start],
[Period1End],
[Period2Start],
[Period2End],
// Help
LET(
Help, TRIM(TEXTSPLIT(
"FUNCTION: →OverLapDaysλ(Period1Start, Period1End, Period2Start, Period2End)¶" &
"DESCRIPTION: →Count days shared between two sets of periods.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-overlapdays%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" & "Period1Start →(Required) First period's start date.¶" &
" Period1End →(Required) First period's end date.¶" &
" Period2Star →(Required) Second period's start date.¶" &
" Period2End →(Required) Decond period's end date.¶" & "EXAMPLES: →¶" &
" Formula →OverLapDaysλ(08, 14, 10, 17)¶" &
" Result →5¶" &
" Formula →OverLapDaysλ(10, 20, 11, 18)¶" &
" Result →8¶" &
" Formula →OverLapDaysλ(""1/17/23"", ""1/25/23"", ""1/7/23"", ""1/18/23"")¶" &
" Result →12",
"→","¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED(Period1Start),
ISOMITTED(Period1End),
ISOMITTED(Period2Start),
ISOMITTED(Period2End)
),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtP1Start, IF(ISNUMBER(Period1Start), Period1Start, DATEVALUE(Period1Start)),
CvtP1End, IF(ISNUMBER(Period1End), Period1End, DATEVALUE(Period1End)),
CvtP2Start, IF(ISNUMBER(Period2Start), Period2Start, DATEVALUE(Period2Start)),
CvtP2End, IF(ISNUMBER(Period2End), Period2End, DATEVALUE(Period2End)),
// Procedure
MinOfEnd, IF(Period2End <= Period1End, Period2End, Period1End),
MaxOfStart, IF(Period2Start >= Period1Start, Period2Start, Period1Start),
Days, MinOfEnd - MaxOfStart + 1,
Result, IF(Days > 0, Days, 0),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: PeriodLabelλ
DESCRIPTION:*//**Creates a lable for a date based on period interval*/
/* REVISIONS: Date Developer Description
2023-Feb-25 Craig Hatmaekr Copyright
2023-Apr-14 Craig Hatmaker Added Help
*/
PeriodLabelλ = LAMBDA(
// Parameter Declarations
[Date],
[Interval],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →PeriodLabelλ(Date, [Interval])¶" &
"DESCRIPTION: →Creates a label for a date based on period interval¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-periodlabel%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" Date →(Required) A single date or array of date(s) where the first date is the first date of a calendar year.¶" &
" Interval →(Optional) Must be one of: ¶" &
"→D=Daily¶" &
"→W=Weekly¶" &
"→I=Weekly (using ISO week number)¶" &
"→M=Monthly¶" &
"→Q=Quarterly¶" &
"→S=Semi-Annually¶" & "→A or Y=Annually¶" &
"EXAMPLES: →¶" &
" Formula →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""D"")¶" &
" Result →2023-Feb-26¶" &
" Formula →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""M"")¶" &
" Result →2023-Feb¶" &
" Formula →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""Q"")¶" &
" Result →2023:Q01¶" &
" Formula →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""Y"")¶" &
" Result →2023",
"→","¶"
)
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED(Date),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtDate, IF(ISOMITTED(Date), FALSE, IF(ISNUMBER(Date), Date, DATEVALUE(Date))),
Interval, IF(ISOMITTED(Interval), "M", Interval),
// Procedure
Result, SWITCH(
Interval,
"D", TEXT(Date, "yyyy-mmm-dd"),
"W", YEAR(Date) & ":W" & TEXT(QUOTIENT(Date - DATE(YEAR(Date), 1, 0) - 1, 7) + 1, "00"),
"I", YEAR(Date) & ":W" & TEXT(ISOWEEKNUM(Date), "00"),
"M", TEXT(Date, "YYYY-MMM"),
"Q", YEAR(Date) & ":Q" & QUOTIENT(MONTH(Date) - 1, 3) + 1,
"S", YEAR(Date) & ":S" & QUOTIENT(MONTH(Date) - 1, 6) + 1,
"A", TEXT(Date, "YYYY"),
"Y", TEXT(Date, "YYYY"),
#VALUE!
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Periodsλ
DESCRIPTION:*//**Determine the number of periods from Starts to Date2 in Days, Weeks, Months, Quarters, or Years.*/
/* REVISIONS: Date Developer Description
Apr 06,2023 Craig Hatmaker Copyright
Aug 17,2023 Craig Hatmaker Work with arrays of dates
Feb 18 2025 Craig Hatmaker Fixed DATEDIF for days. Thanks to Paul Reeves for catching this
*/
Periodsλ = LAMBDA(
// Parameter Declarations
[Starts],
[Ends],
[Intervals],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Periodsλ(Starts, Ends, [Intervals])¶" &
"DESCRIPTION: →Determine the number of periods from date1 to date 2 inclusive.¶" &
"→Differences to Excel4.0's DateDif are:¶" &
"→* Interval includes Weeks & Quarters¶" &
"→* End Date is inclusive¶" &
"→* Returns negative values if Date1 is after Date2¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-periods%CE%BB¶" &
"VERSION: →BXL: Feb 18 2025¶" &
"PARAMETERS: →¶" &
" Starts →(Required) First date (normally older). This can be a single number or array.¶" &
" Ends →(Required) Second date (normally newer). This must be the same shape (# of rows & columns) as Date1.¶" &
" Intervals →(Optional) Days, Weeks, Months (default), Quarters, or Years (D, W, M, Q, Y)¶" &
"EXAMPLES: →¶" &
" Formula →=Periodsλ(""3/31/2023"", ""5/15/2023"")¶" &
" Result →2¶" &
" Formula →=Periodsλ(""5/15/2023"", ""3/31/2023"")¶" &
" Result →-2¶" &
" Formula →=Periodsλ(""1/15/2024"", ""1/16/2023"", , FALSE)¶" &
" Result →-12¶" &
" Formula →=Periodsλ(""1/15/2024"", ""1/16/2023"", ""W"")¶" &
" Result →-53",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR(ISOMITTED(Starts), ISOMITTED(Ends)),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtStarts, IF(ISOMITTED(Starts), FALSE, IF(ISNUMBER(Starts), Starts, DATEVALUE(Starts))),
CvtEnds, IF(ISOMITTED(Ends), FALSE, IF(ISNUMBER(Ends), Ends, DATEVALUE(Ends))),
Intervals, IF(ISOMITTED(Intervals), "M", Intervals),
// Constant Declarations
MPY, 12, //Months Per Year
MPQ, 3, //Months Per Quarter
DPW, 7, //Days Per Week
// Procedure
Rows, MAX(ROWS(CvtStarts), ROWS(CvtEnds)),
Cols, MAX(COLUMNS(CvtStarts), COLUMNS(CvtEnds)),
Result, MAKEARRAY( Rows, Cols,
LAMBDA(R, C,
LET(
DateOne, IF(COUNTA(CvtStarts) = 1, CvtStarts, INDEX(CvtStarts, R, C)),
DateTwo, IF(COUNTA(CvtEnds) = 1, CvtEnds, INDEX(CvtEnds, R, C)),
Interval, IF(COUNTA(Intervals) = 1, Intervals, INDEX(Intervals, R, C)),
Earliest, MIN(DateOne, DateTwo),
Latest, MAX(DateOne, DateTwo), //+ 1,
Sign, SIGN(MAX(DateTwo - DateOne, 1)),
Periods, SWITCH(
Interval,
"D", Latest - Earliest + 1,
"W", INT(DATEDIF(Earliest, Latest, "D") / DPW),
"M", DATEDIF(Earliest, Latest, "M"),
"Q", INT(DATEDIF(Earliest, Latest, "M") / MPQ),
"Y", DATEDIF(Earliest, Latest, "Y")
),
Result, Sign * Periods,
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: ScheduleRatesλ
DESCRIPTION:*//**Schedule rates for one item that persists until replaced in a timeline.*/
/* REVISIONS: Date Developer Description
Jun 01 2022 Craig Hatmaker Copyright
Apr 12 2023 Craig Hatmaker Added Help
Mar 07 2025 Craig Hatmaker Added EndDates? and Filter Argument
*/
ScheduleRatesλ = LAMBDA(
// Parameter Declarations
[Rates],
[EffectiveDates],
[Timeline],
[EndDates?],
[ItemIDs],
[IncludeIDs],
// Help
LET(
Help, TRIM(
TEXTSPLIT(
"FUNCTION: →ScheduleRatesλ(Rates, EffectiveDates, Timeline, [EndDates?], [ItemIDs], [IncludeIDs])¶" &
"DESCRIPTION: →Schedule rates for one item that persists until replaced in a timeline.¶" &
"NOTE! →If 2 rates are scheduled within the same period, only the last rate will be selected.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-schedulerates%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS:→¶" &
" Rates →(Required) Rates to be scheduled and persist until replaced.¶" &
" EffectiveDates→(Required) Dates determining when a rate is effective.¶" &
" Timeline →(Required) The model's timeline.¶" &
" EndDates? →(Optional) TRUE=Timeline displays period end dates. Default = TRUE¶" &
" ItemIDs →(Optional) A list that identifies what item in a group each rate applies to.¶" &
" IncludeIDs →(Optional) A list of one or more RateIDs to include. One ID per result row.¶" &
"EXAMPLES:→¶" &
" Formula →ScheduleRatesλ( ¶" &
"→ tblAPRs[Effective Date], ¶" &
"→ tblAPRs[APR], ¶" &
"→ Timelineλ( ""2024-01-01"", 12, ""M"", FALSE), ¶" &
"→ TRUE, ¶" &
"→ tblAPRs[[Debt '#]], ¶" &
"→ 1)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED(Rates),
ISOMITTED(EffectiveDates),
ISOMITTED(Timeline)
),
// Optional argument defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Columns, COLUMNS( EffectiveDates),
ItemIDs, IF( ISOMITTED( ItemIDs), EXPAND(1, 1, Columns, 1), ItemIDs),
IncludeIDs, IF( ISOMITTED( IncludeIDs), {1}, IncludeIDs),
// Sort Data
Sorted, SORT( HSTACK( ItemIDs, EffectiveDates, Rates), 2),
SortedItemIDs, CHOOSECOLS(Sorted, 1),
SortedDates, CHOOSECOLS(Sorted, 2),
SortedRates, CHOOSECOLS(Sorted, 3),
// Get start and end dates
StartsAndEnds, DeriveTimelinesλ( Timeline, EndDates?),
Starts, CHOOSEROWS( StartsAndEnds, 1),
Ends, CHOOSEROWS( StartsAndEnds, 2),
// Sort by Effective Dates
// Procedure
Counter, SEQUENCE( COUNTA( IncludeIDs)),
Result, REDUCE( 0, Counter,
LAMBDA( Acc, n,
LET(
IncludeID, INDEX( IncludeIDs, n),
Filter, IncludeID = SortedItemIDs,
FilteredDates, FILTER( SortedDates, Filter),
FilteredRates, FILTER( SortedRates, Filter),
Row, XLOOKUP( Ends, FilteredDates, FilteredRates, 0, -1),
Result, IF( n = 1, Row, VSTACK( Acc, Row)),
Result
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: ScheduleValuesλ
DESCRIPTION:*//**Schedules values in a timeline from a schedule in a table. */
/* REVISIONS: Date Developer Description
Apr 12 2023 Craig Hatmaker Copyright
*/
ScheduleValuesλ = LAMBDA(
// Parameter Declarations
[Values],
[EffectiveDates],
[Timeline],
[EndDates?],
[ItemIDs],
[IncludeIDs],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →ScheduleValuesλ(Values, EffectiveDates, Timeline, [EndDates?], [ItemIDs], [IncludeIDs])¶" &
"DESCRIPTION: →Schedules Values in a timeline.¶" &
"WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-schedulevalues%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS:→¶" &
" Values →(Required) A column or row of values to be scheduled.¶" &
" EffectiveDates→(Required) A column or row of rate effective dates in any order.¶" &
" Timeline →(Required) A row of period dates.¶" &
" EndDates? →(Optional) TRUE=Timeline displays period end dates. Default = TRUE¶" &
" ItemIDs →(Optional) A list that identifies what item in a group each rate applies to.¶" &
" IncludeIDs →(Optional) A list of one or more RateIDs to include. One ID per result row.¶" &
"EXAMPLES:→¶" &
" Formula →ScheduleValuesλ( ¶" &
"→ tblDraws[Draw Amount], ¶" &
"→ tblDraws[Effective Date],¶" &
"→ Timelineλ( ""2024-01-01"", 12, ""M"", FALSE), ¶" &
"→ TRUE, ¶" &
"→ tblDraws[[Debt '#],¶" &
"→ 1)",
"→", "¶"
)
),
// Check inputs - Omitted required arguments
Help?, OR( ISOMITTED( Values),
ISOMITTED( EffectiveDates),
ISOMITTED( Timeline)
),
// Optional argument defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
ItemIDs, IF( ISOMITTED( ItemIDs), {1}, ItemIDs),
IncludeIDs, IF( ISOMITTED( IncludeIDs), {1}, IncludeIDs),
// Get start and end dates
StartsAndEnds, DeriveTimelinesλ( Timeline, EndDates?),
Starts, CHOOSEROWS( StartsAndEnds, 1),
Ends, CHOOSEROWS( StartsAndEnds, 2),
// Procedure
Rows, ROWS(IncludeIDs),
Cols, COLUMNS(Ends),
Result, MAKEARRAY( Rows, Cols,
LAMBDA( r, c,
LET(
Start, INDEX( Starts, c),
End, INDEX( Ends, c),
Include,INDEX( IncludeIDs, r),
SUM( Values * (Include = ItemIDs) * (EffectiveDates >= Start) * (EffectiveDates <= End))
)
)
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: Timelineλ
DESCRIPTION:*//**Create a horizontal list of period dates*/
/* REVISIONS: Date Developer Description
Aug 19,2023 Craig Hatmaker Copyright
*/
Timelineλ = LAMBDA(
// Parameter Declarations
[StartDate],
[Periods],
[Interval],
[PeriodStarts?],
LET(
// Help
Help, TRIM(TEXTSPLIT(
"FUNCTION: →Timelineλ(StartDate, Periods, Interval, PeriodStarts?)¶" &
"DESCRIPTION: →Creates a horizontal list of period dates.¶" &
"WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-timeline%CE%BB¶" &
"VERSION: →BXL: Feb 24 2025¶" &
"PARAMETERS: →¶" &
" StartDate →(Required) First period's start date. May be entered as text.¶" &
" Periods →(Optional) The number of period in the timeline. Defaults to 12.¶" &
" Interval →(Optional) Period interval: D, W, M, Q or Y (Day, Week, Month <default>, Quarter, or Year)¶" &
" PeriodStarts? →(Optional) TRUE=Display period start dates <default>. FALSE=Period end dates¶" &
"EXAMPLES: →¶" &
" Formula →=Timelineλ(TODAY())¶" &
" Result →A timeline of 12 months starting dates staring with today's date.",
"→", "¶"
)
),
// Named Constants
MPY, 12, //Months Per Year
MPQ, 3, //Months Per Quarter
DPW, 7, //Days Per Week
// Check inputs - Omitted required arguments
Help?, ISOMITTED(StartDate),
// Check inputs - Set optional arguments' defaults and apply convenience conversions
CvtStart, IF( ISOMITTED(StartDate),
FALSE,
IF(ISNUMBER(StartDate), StartDate, DATEVALUE(StartDate))
),
Periods, IF( ISOMITTED(Periods), 12, Periods),
Interval, IF( ISOMITTED(Interval), "M", Interval),
PeriodStarts?, IF(ISLOGICAL(PeriodStarts?), PeriodStarts?, TRUE),
// Procedure
EndDates, N(NOT(PeriodStarts?)),
Result, SWITCH(
Interval,
"Y", EDATE(StartDate, SEQUENCE(1, Periods, EndDates * MPY, MPY)) - EndDates,
"Q", EDATE(StartDate, SEQUENCE(1, Periods, EndDates * MPQ, MPQ)) - EndDates,
"M", EDATE(StartDate, SEQUENCE(1, Periods, EndDates, 1)) - EndDates,
"W", SEQUENCE(1, Periods, StartDate + IF(PeriodStarts?, 0, DPW - 1), DPW),
"D", SEQUENCE(1, Periods, StartDate, 1),
#VALUE!
),
// Return Result
CHOOSE(Help? + 1, Result, Help)
)
);
/* FUNCTION NAME: DeriveTimelinesλ
DESCRIPTION:*//**Create internal start and end timelines based on the model's timeline*/
/* REVISIONS: Date Developer Description
Feb 22 2025 Craig Hatmaker Original Development and copyright
*/
DeriveTimelinesλ = LAMBDA(
// Parameter Declarations
[ModelTimeline],
[EndDates?],
// Help
LET(Help, TRIM(TEXTSPLIT(
"FUNCTION: →DeriveTimelinesλ( ModelTimeline, [EndDates?])¶" &
"DESCRIPTION: →Create internal start and end timelines based on the model's timeline¶" &
"NOTES: →For use by functions that require start and end dates¶" &
"→without requiring models to have both start and end dates¶" &
"WEBPAGE: →<Coming Soon>¶" &
"VERSION: →BXL: Feb 22 2025¶" &
"PARAMETERS:→¶" &
" ModelTimeline →(Required) Either the model's start or end timeline¶" &
" EndDates? →(Optional) TRUE (default) = The model's timeline displays period end dates¶" &
"EXAMPLE:→¶" &
" Usage →=DeriveTimelinesλ( Timeline)",
"→", "¶" )
),
// Check inputs - Omitted required arguments
Help?, ISOMITTED( ModelTimeline),
// Understand the model's timeline
// Set Constants
MpY, 12, //Months Per Year
DpM, 365/MpY, //Days Per month
QpY, 4, //Quarters Per Year
// Determine how many months are in each of the model's periods
FirstPeriod, INDEX( ModelTimeline, 1),
SecondPeriod, INDEX( ModelTimeline, 2),
MpP, @ROUND(( SecondPeriod - FirstPeriod) / DpM, 0), //Months Per Period
// Set timeline dependent defaults
EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?),
Interval, SWITCH( MpP, 1, "M", 3, "Q", 12, "Y"),
PpY, SWITCH( MpP, 1, MpY, 3, QpY, 12, 1), //Periods Per Year
Starts, IF( EndDates?, EDATE( +ModelTimeline + 1, - MpP), ModelTimeline),
Ends, IF( EndDates?, ModelTimeline, EDATE( +ModelTimeline, MpP) - 1),
// Procedure
Result, VSTACK(Starts, Ends),
// Result
CHOOSE(Help? + 1, Result, Help)
)
);
@ikellerer
Copy link

ikellerer commented Jan 5, 2024 •
edited
Loading

First of all: this is a great library and together with the EuSpRIG-paper a great source to learn!

Regarding IsOccurrenceDateλ

The Annual check doesn't work e.g. for a German system as it would need to be TEXT(CvtDates, "MMTT") = TEXT(CvtFirsts, "MMTT") (T (Tag) instead of D (Day)) - this doesn't get translated :-(

I updated the check like this:

    //  Procedure
        SameDay,        DAY( CvtDates) = DAY( CvtFirsts),
        SameMonth,      MONTH(CvtDates)=MONTH(CvtFirsts),
        Test,           ( CvtFirsts <= CvtDates) * 
                        IF(TRIM( CvtLasts) = "", 1, CvtDates <= CvtLasts) *
                        SWITCH(
                            Repeats,
                            "O",    CvtDates = CvtFirsts,
                            "A",    SameDay * SameMonth,

@CHatmaker
Copy link
Author

Jkellerer, Thanks for letting me know. Clever fix. :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment