Last active
September 1, 2026 16:05
-
-
Save CHatmaker/97de2e488640a44771b77667389407b4 to your computer and use it in GitHub Desktop.
BXL 5g Functions for Excel: Date Library 2026
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* Module Contains 5g Compliant functions that deal with dates. */ | |
| /* FUNCTION NAME: Aboutλ | |
| DESCRIPTION:*//**Displays modules function list, version, and Gist and website URLs*/ | |
| /* REVISIONS: Date Developer Description | |
| Jun 26 2026 Craig Hatmaker New Version | |
| Sep 01 2026 Craig Hatmaker Added DateDifλ | |
| */ | |
| Aboutλ = TRIM( | |
| TEXTSPLIT( | |
| "About: →5g functions dealing with dates in models. Suggested module name: BXD¶" & | |
| "Version: →BXL: Jun 26 2026¶" & | |
| "Gist URL: →https://gist.github.com/CHatmaker/fcbd2ffab850a4affc80ac0553cf732a ¶" & | |
| "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.¶" & | |
| " DateDifλ →A correct version of Excel's deprecated DATEDIF function. ¶" & | |
| " 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.¶" & | |
| " Periodsλ →Determine the number of periods from Starts to Ends inclusive¶" & | |
| " PeriodLabelλ →Creates a label for a date based on period interval¶" & | |
| " PeriodStartλ →Determine when the period containing a date of interest starts¶" & | |
| " 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" , | |
| " AdaptToTimelineλ →Adapt a block of calculations to 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-date-library/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.", | |
| "→", | |
| "¶" | |
| ) | |
| ), | |
| // 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-date-library/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.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // 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: /* FUNCTION NAME: DateDifλ | |
| DESCRIPTION:*//**A correct version of Excel's deprecated DATEDIF function*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 31 2026 Aditya Kumar Darak Original development | |
| Sep 01 2026 Craig Hatmaker 5g additions (inline help, comment block, etc.) | |
| */ | |
| DateDifλ = LAMBDA([StartDate], [EndDate], [Units], | |
| // Check required arguments | |
| IF( OR( ISOMITTED( StartDate), | |
| ISOMITTED( EndDate) | |
| ), | |
| TRIM(TEXTSPLIT( | |
| "FUNCTION: →DateDifλ( StartDate, EndDate, [Unit]) ¶" & | |
| "DESCRIPTION: →Determines how many of 'Units' have elapsed between 2 dates.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling ¶" & | |
| "VERSION: →BXL: Sep 01, 2026 (Original by Aditya Kumar Darak) ¶" & | |
| "PARAMETERS:→¶" & | |
| "StartDate →The first date.¶" & | |
| "EndDate →The last date¶" & | |
| "Units →A code designating period type¶" & | |
| "→ Y complete years¶" & | |
| "→ M complete months¶" & | |
| "→ D plain day difference¶" & | |
| "→ YM months left over after the whole years¶" & | |
| "→ YD days left over after the whole year¶" & | |
| "→ MD days left over after the whole months¶" & | |
| "→ NOTE! A reversed date range or an unrecognised unit raises #NUM!", | |
| "→", "¶" )), | |
| LET( | |
| // Set Defaults | |
| Units, IF( ISOMITTED( Units), "M", UPPER(Units)), | |
| // Rough month gap, counting calendar boundaries crossed rather than | |
| // full months elapsed. This will overshoot when the day of the month | |
| // has not come around yet. | |
| RawMonths, 12 * (YEAR( EndDate) - YEAR( StartDate)) + MONTH( EndDate) - MONTH( StartDate), | |
| // Here is the correction. If shifting the start date forward by | |
| // rawMonths lands after the end date, that final month never | |
| // completed, so we drop it. The comparison hands back TRUE (1) or | |
| // FALSE (0), which subtracts cleanly with no IF needed. | |
| WholeMonths, RawMonths - ( EDATE( +StartDate, RawMonths) > EndDate), | |
| // Complete years, taken from the corrected month count | |
| wholeYears, QUOTIENT( WholeMonths, 12), | |
| // The most recent monthly anniversary of the start date. Anything | |
| // after this is the leftover days in "MD". | |
| LastAnniversary,EDATE( StartDate, WholeMonths), | |
| UnitsNumber, XLOOKUP( Units, {"Y","M","D","YM","YD","MD"}, {1,2,3,4,5,6}, 7, 0), | |
| Return, IF( EndDate < StartDate, | |
| #NUM!, | |
| CHOOSE( UnitsNumber, | |
| WholeYears, | |
| WholeMonths, | |
| EndDate - StartDate, | |
| MOD( WholeMonths, 12), | |
| EndDate - EDATE( +StartDate, 12 * WholeYears), | |
| EndDate - LastAnniversary, | |
| #NUM!) | |
| ), | |
| Return | |
| ) | |
| ) | |
| ); | |
| /* 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: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→<coming soon> ¶" & | |
| "VERSION: →BXL: Mar 01 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Date →(Required) Date to determine if in a leap year.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // 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-date-library/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", | |
| "→","¶" | |
| ) | |
| ), | |
| // 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-date-library/5g-overlapdays%CE%BB¶" & | |
| "VERSION: →BXL: Feb 24 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Period1Start →(Required) First period's start date.¶" & | |
| " Period1End →(Required) First period's end date.¶" & | |
| " Period2Start →(Required) Second period's start date.¶" & | |
| " Period2End →(Required) Decond period's end date.", | |
| "→","¶" | |
| ) | |
| ), | |
| // 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: 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¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-date-library/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)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // 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: 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¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-date-library/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", | |
| "→","¶" | |
| ) | |
| ), | |
| // 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: PeriodStartλ | |
| DESCRIPTION:*//**Determine when the period containing a date of interest starts*/ | |
| /* REVISIONS: Date Developer Description | |
| Apr 13 2024 Craig Hatmaker Copyright | |
| May 16 2025 Craig Hatmaker Use DeriveTimelinesλ | |
| */ | |
| PeriodStartλ = LAMBDA( | |
| // Parameter Declaration | |
| [Timeline], | |
| [EndDates?], | |
| [Date], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →PeriodStartλ( Timeline, [EndDates?], Date )¶" & | |
| "DESCRIPTION: →Determine when the period containing a date of interest starts.¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→<coming soon>¶" & | |
| "VERSION: →BXL: May 16 2025¶" & | |
| "PARAMETERS: →¶" & | |
| " Timeline →(Required) Number of months in each period¶" & | |
| " EndDates? →(Optional) Default=TRUE indicating the timeline shows period end dates.¶" & | |
| " Date →(Required) Any start date within a timeline.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED( Date), | |
| ISOMITTED( TImeline) | |
| ), | |
| // Set Defaults | |
| EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?), | |
| // Get start and end dates | |
| StartsAndEnds, DeriveTimelinesλ( Timeline, EndDates?), | |
| Starts, CHOOSEROWS( StartsAndEnds, 1), | |
| Ends, CHOOSEROWS( StartsAndEnds, 2), | |
| // Procedure | |
| Result, XLOOKUP(Date, Starts, Starts,,-1), | |
| // Return Result or Help | |
| 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 | |
| May 14 2025 Craig Hatmaker Added EndDates? and Filter Argument | |
| */ | |
| ScheduleRatesλ = LAMBDA( | |
| // Parameter Declarations | |
| [Timeline], | |
| [EndDates?], | |
| [Rates], | |
| [EffectiveDates], | |
| [ItemIDs], | |
| [IncludeIDs], | |
| // Help | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleRatesλ(Timeline, [EndDates?], Rates, EffectiveDates, [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.¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-date-library/5g-schedulerates%CE%BB¶" & | |
| "VERSION: →BXL: Feb 24 2025¶" & | |
| "PARAMETERS:→¶" & | |
| " Timeline →(Required) The model's timeline.¶" & | |
| " EndDates? →(Optional) TRUE=Timeline displays period end dates. Default = TRUE¶" & | |
| " Rates →(Required) Rates to be scheduled and persist until replaced.¶" & | |
| " EffectiveDates→(Required) Dates determining when a rate is effective.¶" & | |
| " ItemIDs →(Optional) A list that identifies what item in a group each rate applies to.¶" & | |
| " IncludeIDs →(Optional) A list of one or more ItemIDs to include. One ID per result row.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| Help?, OR( ISOMITTED(Rates), | |
| 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 | |
| Counter, SEQUENCE( , ROWS( IncludeIDs)), | |
| Result, REDUCE( "", Counter, | |
| LAMBDA( Acc, n, | |
| LET( | |
| IncludeID, INDEX( IncludeIDs, n), | |
| Filter, IncludeID = ItemIDs, | |
| FilteredDates, FILTER( EffectiveDates, Filter), | |
| FilteredRates, FILTER( Rates, Filter), | |
| Row, XLOOKUP( Ends, FilteredDates, FilteredRates, 0, -1), | |
| Stack, INDEX( IncludeIDs, n), | |
| 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 | |
| [Timeline], | |
| [EndDates?], | |
| [Values], | |
| [EffectiveDates], | |
| [ItemIDs], | |
| [IncludeIDs], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleValuesλ(Timeline, [EndDates?], Values, EffectiveDates, [ItemIDs], [IncludeIDs])¶" & | |
| "DESCRIPTION: →Schedules Values in a timeline.¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/¶" & | |
| "→5g-component-libraries/5g-date-library/5g-schedulevalues%CE%BB ¶" & | |
| "VERSION: →BXL: Feb 24 2025¶" & | |
| "PARAMETERS:→¶" & | |
| " Timeline →(Required) A row of period dates.¶" & | |
| " EndDates? →(Optional) TRUE=Timeline displays period end dates. Default = TRUE¶" & | |
| " Values →(Required) A column or row of values to be scheduled.¶" & | |
| " EffectiveDates→(Required) A column or row of rate effective dates in any order.¶" & | |
| " ItemIDs →(Optional) A list that identifies what item in a group each value applies to.¶" & | |
| " IncludeIDs →(Optional) A list of one or more ItemIDs to include. One ID per result row.", | |
| "→", "¶" | |
| ) | |
| ), | |
| // 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-date-library/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", | |
| "→", "¶" | |
| ) | |
| ), | |
| // 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 | |
| May 16 2025 Craig Hatmaker Added D and W intervals | |
| Jun 17 2025 Craig Hatmaker Efficiency improvements | |
| Nov 26 2025 Craig Hatmaker Simplified | |
| Jun 26 2026 Craig Hatmaker Conform to new template | |
| */ | |
| DeriveTimelinesλ = LAMBDA( | |
| // Parameter Declarations | |
| [ModelTimeline], | |
| [EndDates?], | |
| // Help | |
| IF( ISOMITTED( ModelTimeline), | |
| 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: May 28 2025¶" & | |
| "PARAMETERS:→¶" & | |
| " ModelTimeline →(Required) Either the model's start or end timeline¶" & | |
| " EndDates? →(Optional) TRUE (default) = The model's timeline displays period end dates.", | |
| "→", "¶" ) | |
| ), | |
| LET( | |
| // Set defaults | |
| EndDates?, IF( ISOMITTED( EndDates?), TRUE, EndDates?), | |
| // Set Constants | |
| IntervalDays, {1;7;28;88;360}, | |
| IntervalPeriod, {"D";"W";"M";"Q";"Y"}, | |
| // Get First and Last Periods | |
| FirstPeriod, TAKE( ModelTimeline, , 1), | |
| LastPeriod, TAKE( ModelTimeline, , -1), | |
| // Determine how many days are in model's periods | |
| DpP, INDEX( ModelTimeline, 2) - FirstPeriod, | |
| Interval, XLOOKUP( DpP, IntervalDays, IntervalPeriod, ,-1), | |
| // Select interval formula | |
| DateFunction, IF( EndDates?, | |
| IF( MONTH( FirstPeriod) = MONTH( FirstPeriod + 1), EDATE, EOMONTH), | |
| IF( DAY( FirstPeriod) = 1, EOMONTH, EDATE) | |
| ), | |
| // Calculate period to add to either the beginning or end of model's timeline | |
| NewPeriod, IF( EndDates?, | |
| SWITCH( Interval, | |
| "D", FirstPeriod - 1, | |
| "W", FirstPeriod - 6, | |
| "M", DateFunction( FirstPeriod, -1), | |
| "Q", DateFunction( FirstPeriod, -3), | |
| "Y", DateFunction( FirstPeriod, -12) | |
| ), | |
| SWITCH( Interval, | |
| "D", LastPeriod + 1, | |
| "W", LastPeriod + 6, | |
| "M", DateFunction( LastPeriod, 1), | |
| "Q", DateFunction( LastPeriod, 3), | |
| "Y", DateFunction( LastPeriod, 12) | |
| ) | |
| ), | |
| // Extend Model Timeline | |
| Extended, IF( EndDates?, | |
| HSTACK( NewPeriod, ModelTimeline), | |
| HSTACK( ModelTimeline, NewPeriod) | |
| ), | |
| // Calculate End Dates | |
| Starts, IF( EndDates?, | |
| DROP( Extended, ,-1) + 1, | |
| ModelTimeline), | |
| Ends, IF( EndDates?, | |
| ModelTimeline, | |
| DROP( Extended, , 1) -1), | |
| // Procedure | |
| Result, VSTACK(Starts, Ends, IF(Starts, Interval)), | |
| Result | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: AdaptToTimelineλ | |
| DESCRIPTION:*//**Positions and adjusts an array to fit in a model's timeline*/ | |
| /* REVISIONS: Date Developer Description | |
| Nov 27 2025 Craig Hatmaker Copyright | |
| Dec 06 2025 Craig Hatmaker Fixed Opn | |
| Jun 18 2026 Craig Hatmaker Fixed Avg, ANZ, Sum | |
| */ | |
| AdaptToTimelineλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [ValuesRowTypes], | |
| [ValuesStarts], | |
| [ValuesEnds], | |
| [ModelStarts], | |
| [ModelEnds], | |
| // Check inputs - Omitted required arguments | |
| IF( OR( ISOMITTED( Values), | |
| ISOMITTED( ValuesRowTypes), | |
| ISOMITTED( ValuesStarts), | |
| ISOMITTED( ValuesEnds), | |
| ISOMITTED( ModelStarts), | |
| ISOMITTED( ModelEnds) | |
| ), | |
| TRIM(TEXTSPLIT( | |
| "FUNCTION: →AdaptToTimelineλ( Values, ValuesRowTypes,¶" & | |
| "→ ValuesStarts, ValuesEnds, ModelStarts, ModelEnds, )¶" & | |
| "DESCRIPTION: →Adapts an array based on one timeline, to the model's timeline.¶" & | |
| "WEBPAGE: →<Coming Soon>¶" & | |
| "VERSION: →BXL: Nov 27 2025¶" & | |
| "PARAMETERS:→¶" & | |
| " Values →(Required) 2 dimensional array/range containing values to be placed in the¶" & | |
| "→model's timeline.¶" & | |
| " ValuesRowTypes→(Required) A column of indicators for each row where values row contains: ¶" & | |
| "→ Avg=Average values in period.¶" & | |
| "→ ANZ=Average non-zero values in period.¶" & | |
| "→ Sum=Transactions - SUM'd by model period.¶" & | |
| "→ Opn=Opening Balances - Pulled from first Value fitting in a model period.¶" & | |
| "→ Cls=Closing Balances - Pulled from last Value fitting in a model period.¶" & | |
| "→ Spc=Blank lines.¶" & | |
| " ValuesStarts →(Required) The values timeline in period start dates¶" & | |
| " ValuesEnds →(Required) The values timeline in period End dates¶" & | |
| " ModelStarts →(Required) The model's timeline period start dates¶" & | |
| " ModelEnds →(Required) The model's timeline period End dates", | |
| "→", "¶" ) | |
| ), | |
| LET( | |
| // Pre-calcs | |
| Rows, ROWS( Values), | |
| Cols, COLUMNS( ModelStarts), | |
| // Define functions that handle processing each row type. | |
| Functions, MATCH( ValuesRowTypes, {"Opn", "Cls", "Avg", "ANZ", "Sum", "Spc"}, 0), | |
| // Procedure | |
| Result, REDUCE( 0, SEQUENCE( Rows), | |
| LAMBDA( Acc, r, | |
| LET( | |
| Function, INDEX( Functions, r), | |
| Row, CHOOSEROWS( Values, r), | |
| Effective, N((TAKE( ValuesStarts, , 1) <= ModelStarts) * (TAKE( ValuesEnds, , -1) >= ModelEnds)), | |
| Line, CHOOSE( Function, | |
| // Open | |
| XLOOKUP( ModelStarts, ValuesStarts, Row, 0, 1), // * Effective, | |
| // Close | |
| XLOOKUP( ModelEnds, ValuesStarts, Row, 0, -1), //* Effective, | |
| // Average | |
| MAP( ModelStarts, ModelEnds, | |
| LAMBDA( MStart, MEnd, | |
| AVERAGE(( MStart <= ValuesEnds) * (MEnd >= ValuesStarts) * Row))), | |
| // Average non-zero | |
| MAP( ModelStarts, ModelEnds, | |
| LAMBDA( MStart, MEnd, | |
| LET( | |
| Values, ( MStart <= ValuesEnds) * (MEnd >= ValuesStarts) * Row, | |
| Filter, FILTER( Values, Values <> 0) , | |
| IFERROR(AVERAGE(Filter), 0) | |
| ))), | |
| // Sum | |
| MAP( ModelStarts, ModelEnds, | |
| LAMBDA( MStart, MEnd, | |
| SUM(( MStart <= ValuesEnds) * (MEnd >= ValuesStarts) * Row))) , | |
| // Space | |
| IF( ModelStarts, "") | |
| ), | |
| Result, IF( r = 1, Line, VSTACK( Acc, Line)), | |
| Result | |
| ) | |
| ) | |
| ), | |
| Result | |
| ) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment