Forked from CHatmaker/BXL LAMBDA Excel Dates.txt
Last active
January 5, 2024 13:00
-
-
Save ikellerer/683b1d791fbd44d4f9af031d1d6c102d to your computer and use it in GitHub Desktop.
Excel CBSE Compliant LAMBDA (5G Modeling) for Dates
This file contains 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 CBSE Compliant LAMBDAs 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 | |
| Oct 02 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| Aboutλ = TRIM( | |
| TEXTSPLIT( | |
| "About: →CBSE compliant LAMBDAs dealing with dates. Suggested module name: BXD¶" & | |
| "Version: →Oct 02 2023¶" & | |
| "Gist URL: →https://gist.github.com/CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b ¶" & | |
| "Website: →https://sites.google.com/site/beyondexcel/home/excel-library/ ¶" & | |
| "→¶" & | |
| "Function →Description¶" & | |
| "Aboutλ →Produces this table¶" & | |
| "CreateEndDatesλ →(Deprecated) Use Timelineλ¶" & | |
| "CreateStartDatesλ →(Deprecated) Use Timelineλ¶" & | |
| "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¶" & | |
| "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¶" & | |
| "ScheduleRatesλ →Schedule rates that persist until replaced in a timeline.¶" & | |
| "ScheduleRatesByItemsλ →Schedule rates that persist until replaced in a timeline for each item in a list.¶" & | |
| "ScheduleValuesλ →Schedules values in a timeline. ¶" & | |
| "ScheduleValuesByItemsλ →Schedules values in a timeline for each item in a list.¶" & | |
| "Timelineλ →Creates a horizontal list of start or end dates for a 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| CountDOWλ = LAMBDA( | |
| // Parameter Declarations | |
| [Starts], | |
| [Ends], | |
| [DayOfWeek], | |
| [Diagnostics], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →CountDOWλ(Starts, Ends, DayOfWeek, [Diagnostics])¶" & | |
| "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¶" & | |
| "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=Saturday, 7=Sunday.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "2 →=CountDOWλ(""3/22/2010"", ""4/10/2010"", 1)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Starts), | |
| ISOMITTED( Ends), | |
| ISOMITTED( DayOfWeek) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtStarts, IF( ISOMITTED( Starts), | |
| FALSE, | |
| IF( ISNUMBER( Starts), Starts, DATEVALUE( Starts)) | |
| ), | |
| CvtEnds, IF( ISOMITTED( Ends), | |
| FALSE, | |
| IF( ISNUMBER( Ends), Ends, DATEVALUE( Ends)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtStarts)), | |
| OR( ISERROR( CvtEnds)), | |
| OR( ISERROR( DayOfWeek)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( Starts), ISERROR(CvtStarts)), | |
| FALSE, | |
| OR(CvtStarts < 1)), | |
| DVArg2a, IF( OR( ISOMITTED( Ends), ISERROR(CvtEnds)), | |
| FALSE, | |
| AND(CvtEnds < 1)), | |
| DVArg2b, IF( OR( ISOMITTED( Ends), ISERROR(CvtEnds), ISOMITTED( Starts), ISERROR(CvtStarts)), | |
| FALSE, | |
| AND( COUNTA( CvtEnds) <> 1, | |
| OR( ROWS( CvtEnds) <> ROWS(CvtStarts), COLUMNS( CvtEnds) <> COLUMNS(CvtStarts)))), | |
| DVArg3a, IF( OR( ISOMITTED( DayOfWeek), ISERROR(DayOfWeek)), | |
| FALSE, | |
| OR( DayOfWeek < 1 , DayOfWeek > 7)), | |
| DVArg3b, IF( OR( ISOMITTED( DayOfWeek), ISERROR(DayOfWeek), ISOMITTED( Starts), ISERROR(CvtStarts)), | |
| FALSE, | |
| AND( COUNTA( DayOfWeek) <> 1, | |
| OR( ROWS( DayOfWeek) <> ROWS(CvtStarts), COLUMNS( DayOfWeek) <> COLUMNS(CvtStarts)))), | |
| DVErrors, VSTACK( DVArg1, DVArg2a, DVArg2b, DVArg3a, DVArg3b), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Starts is missing. Starts must be one or more start dates (text or numeric) in a row or column."; | |
| "Ends is missing. Ends must be end dates (text or numeric) with 1 or the same number of rows and columns as start dates."; | |
| "DayOfWeek is missing. DayOfWeek must be a 1 for Sunday through 7 for Saturday"; | |
| "Starts contains errors in cells. Resolve errors first."; | |
| "Ends contains errors in cells. Resolve errors first."; | |
| "DayOfWeek contains errors in cells. Resolve errors first."; | |
| "Starts must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Ends must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Ends must have 1 date or the same number rows and columns as Starts"; | |
| "DayOfWeek must be a 1 for Sunday through 7 for Saturday"; | |
| "DayOfWeek must have 1 entry or the same number of rows and columns as Starts"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Interim, INT( ( ( CvtEnds - CvtStarts) + WeekDay( CvtStarts - DayOfWeek)) / 7), | |
| Result, Interim * (Interim > 0), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| CountDOWInMonthλ = LAMBDA( | |
| // Parameter Declarations | |
| [DateInMonth], | |
| [DayOfWeek], | |
| [Diagnostics], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →CountDOWInMonthλ(DateInMonth, DayOfWeek, [Diagnostics])¶" & | |
| "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¶" & | |
| "PARAMETERS: →¶" & | |
| "DateInMonth →(Required) Any date within the month to count¶" & | |
| "DayOfWeek →(Required) 1=Sunday, 7=Saturday.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "5 →=CountDOWInMonthλ(""1/1/2023"", 1)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( DateInMonth), | |
| ISOMITTED( DayOfWeek) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtDates, IF( ISOMITTED( DateInMonth), | |
| FALSE, | |
| IF( ISNUMBER( DateInMonth), DateInMonth, DATEVALUE( DateInMonth)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( DateInMonth)), | |
| OR( ISERROR( DayOfWeek)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( CvtDates), ISERROR(CvtDates)), | |
| FALSE, | |
| OR(CvtDates < 1)), | |
| DVArg2a, IF( OR( ISOMITTED( DayOfWeek), ISERROR(DayOfWeek)), | |
| FALSE, | |
| OR( DayOfWeek < 1 , DayOfWeek > 7)), | |
| DVArg2b, IF( OR( ISOMITTED( DayOfWeek), ISERROR(DayOfWeek), ISOMITTED( DateInMonth), ISERROR(CvtDates)), | |
| FALSE, | |
| AND( COUNTA( DayOfWeek) <> 1, | |
| OR( ROWS( DayOfWeek) <> ROWS(CvtDates), COLUMNS( DayOfWeek) <> COLUMNS(CvtDates)))), | |
| DVErrors, VSTACK( DVArg1, DVArg2a, DVArg2b), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"DateInMonth is missing. DateInMonth must be one or more dates (text or numeric) in a row or column."; | |
| "DayOfWeek is missing. DayOfWeek must be a 1 for Sunday through 7 for Saturday"; | |
| "DateInMonth contains errors in cells. Resolve errors first."; | |
| "DayOfWeek contains errors in cells. Resolve errors first."; | |
| "DateInMonth must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "DayOfWeek must be a 1 for Sunday through 7 for Saturday"; | |
| "DayOfWeek must have 1 entry or the same number of rows and columns as DateInMonth"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Days, DAY(EOMONTH( CvtDates, 0)) - 1, | |
| MonthStart, EOMONTH( CvtDates, -1 ) + 1, | |
| Result, INT( ( Days + WeekDay( MonthStart - DayOfWeek)) / 7), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| IsBetweenλ = LAMBDA( | |
| // Parameter Declarations | |
| [Values], | |
| [Low], | |
| [High], | |
| [Inclusive], | |
| [Diagnostics], | |
| // Help | |
| LET(Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →IsBetweenλ(Values, Low, High, [Inclusive], [Diagnostics])¶" & | |
| "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 ¶" & | |
| "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)¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES :→¶" & | |
| "Result →Formula¶" & | |
| "FALSE →=IsBetweenλ(1, 2, 4)¶" & | |
| "TRUE →=IsBetweenλ(Today(), Today(), Today()+1)¶" & | |
| "FALSE →=IsBetweenλ(""B"", ""B"", ""D"", FALSE)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Error Messages | |
| ErrMsgs, {"Enter Values. Values must have one or more values"; | |
| "Enter Low. Low must contain one value or as many values as Values"; | |
| "Enter High. High must contain one value or as many values as Values"; | |
| "Values contains errors. Resolve errors first."; | |
| "Low contains errors. Resolve errors first."; | |
| "High contains errors. Resolve errors first."; | |
| "Low can be a single entry or the same number of entries as Values."; | |
| "High can be a single entry or the same number of entries as Values."}, | |
| // Check inputs - Set optional arguments defaults | |
| Inclusive, IF( ISLOGICAL( Inclusive), Inclusive, TRUE), | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Values), | |
| ISOMITTED( Low), | |
| ISOMITTED( High) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Errors in arguments | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( Values)), | |
| OR( ISERROR( Low)), | |
| OR( ISERROR( High)) | |
| ), | |
| // Check inputs - Element Count Errors | |
| ErrLow, AND(NOT( ISOMITTED( Values)), | |
| NOT( OR( ISERROR(Values))), | |
| AND( COUNTA( Low) <> 1, | |
| COUNTA( Low) <> COUNTA( High), | |
| COUNTA( Values) <> 1), | |
| COUNTA( Values) <> COUNTA( Low) | |
| ), | |
| ErrHigh, AND(NOT( ISOMITTED( Values)), | |
| NOT( OR( ISERROR(Values))), | |
| AND( COUNTA( High) <> 1, COUNTA( High) <> COUNTA( Low), | |
| COUNTA( Values) <> 1), | |
| COUNTA( Values) <> COUNTA( High) | |
| ), | |
| CountErrors, VSTACK( | |
| AND( NOT( OR( ISERROR(Low))), ErrLow), | |
| AND( NOT( OR( ISERROR(High))), ErrHigh) | |
| ), | |
| // Any input errors detected? | |
| AnyErrors?, OR( OmittedArgs, ErrorsInArgs, CountErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors, VSTACK( | |
| OmittedArgs, | |
| Diagnostics * ErrorsInArgs = 1, | |
| Diagnostics * CountErrors = 1 | |
| ), | |
| Messages, FILTER(ErrMsgs, Errors, ""), | |
| // Procedure | |
| Result, IF(Inclusive, (Values >= Low) * (Values <= High) = 1, (Values > Low) * (Values < High) = 1), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: IsOccurrenceDateλ | |
| DESCRIPTION:*//**Returns TRUE if Date passed 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| IsOccurrenceDateλ = LAMBDA( | |
| // Parameter declarations | |
| [Dates], | |
| [FirstOccurence], | |
| [LastOccurence], | |
| [Repeats], | |
| [Diagnostics], | |
| // Help | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →IsOccurrenceDateλ(Dates, FirstOccurence, [LastOccurence], [Repeats], [Diagnostics])¶" & | |
| "DESCRIPTION: →Returns TRUE if Date passed 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¶" & | |
| "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¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "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 | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Dates), | |
| ISOMITTED( FirstOccurence) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtDates, IF( ISOMITTED( Dates), | |
| FALSE, | |
| 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)), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtDates)), | |
| OR( ISERROR( CvtFirsts)), | |
| OR( ISERROR( CvtLasts)), | |
| OR( ISERROR( Repeats)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( Dates), ISERROR( CvtDates)), | |
| FALSE, | |
| OR(CvtDates < 1)), | |
| DVArg2, IF( OR( ISOMITTED( FirstOccurence), ISERROR( CvtFirsts)), | |
| FALSE, | |
| OR(CvtFirsts < 1)), | |
| DVArg3a, IF( OR( ISOMITTED( LastOccurence), ISERROR( CvtLasts)), | |
| FALSE, | |
| OR( CvtLasts < 0)), | |
| DVArg3b, IF( OR( ISOMITTED( FirstOccurence), ISERROR(CvtLasts), ISOMITTED( LastOccurence), ISERROR(CvtLasts)), | |
| FALSE, | |
| OR( ROWS( CvtFirsts) <> ROWS( CvtLasts), | |
| ROWS( CvtFirsts) <> ROWS( Repeats), | |
| COLUMNS( CvtFirsts) <> COLUMNS( CvtLasts), | |
| COLUMNS( CvtFirsts) <> COLUMNS( Repeats))), | |
| DVArg4, IF( OR( ISOMITTED( Repeats), ISERROR( Repeats)), | |
| FALSE, | |
| OR(ISERROR(SEARCH(Repeats, "OASQMBWXD")))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3a, DVArg3b, DVArg4), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Dates is missing. Dates must be one or more dates (text or numeric) in a row or column."; | |
| "FirstOccurence is missing. FirstOccurence must be teh first date (text or numeric) when something happens."; | |
| "Dates contains errors in cells. Resolve errors first."; | |
| "FirstOccurence contains errors in cells. Resolve errors first."; | |
| "LastOccurence contains errors in cells. Resolve errors first."; | |
| "Repeats contains errors in cells. Resolve errors first."; | |
| "Dates must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "FirstOccurence must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "LastOccurence must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "LastOccurence, FirstOccurence, and Repeats must have same number rows and columns"; | |
| "Repeats must be one of O (One time), A (Annually), S (Semi-annally), Q (Quarterly), M (Monthly), B (Biweekly), W (Weekly), D (Daily), X (Workday USA)"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // 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, | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| OverLapDaysλ = LAMBDA( | |
| // Parameter Declarations | |
| [Period1Start], | |
| [Period1End], | |
| [Period2Start], | |
| [Period2End], | |
| [Diagnostics], | |
| // Help | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →OverLapDaysλ(Period1Start, Period1End, Period2Start, Period2End, [Diagnostics])¶" & | |
| "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¶" & | |
| "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.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "5 →=OverLapDaysλ(08, 14, 10, 17)¶" & | |
| "8 →=OverLapDaysλ(10, 20, 11, 18)¶" & | |
| "12 →=OverLapDaysλ(""1/17/23"", ""1/25/23"", ""1/7/23"", ""1/18/23"")", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Period1Start), | |
| ISOMITTED( Period1End), | |
| ISOMITTED( Period2Start), | |
| ISOMITTED( Period2End) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtP1Start, IF( ISOMITTED( Period1Start), | |
| FALSE, | |
| IF( ISNUMBER( Period1Start), Period1Start, DATEVALUE( Period1Start)) | |
| ), | |
| CvtP1End, IF( ISOMITTED( Period1End), | |
| FALSE, | |
| IF( ISNUMBER( Period1End), Period1End, DATEVALUE( Period1End)) | |
| ), | |
| CvtP2Start, IF( ISOMITTED( Period2Start), | |
| FALSE, | |
| IF( ISNUMBER( Period2Start), Period2Start, DATEVALUE( Period2Start)) | |
| ), | |
| CvtP2End, IF( ISOMITTED( Period2End), | |
| FALSE, | |
| IF( ISNUMBER( Period2End), Period2End, DATEVALUE( Period2End)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtP1Start)), | |
| OR( ISERROR( CvtP1End)), | |
| OR( ISERROR( CvtP2Start)), | |
| OR( ISERROR( CvtP2End)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( Period1Start), ISERROR( CvtP1Start)), | |
| FALSE, | |
| OR(CvtP1Start < 1)), | |
| DVArg2, IF( OR( ISOMITTED( Period1End), ISERROR( CvtP1End)), | |
| FALSE, | |
| OR(CvtP1End < 1)), | |
| DVArg3, IF( OR( ISOMITTED( Period2Start), ISERROR( CvtP2Start)), | |
| FALSE, | |
| OR(CvtP2Start < 1)), | |
| DVArg4, IF( OR( ISOMITTED( Period2End), ISERROR( CvtP2End)), | |
| FALSE, | |
| OR(CvtP2End < 1)), | |
| DVArg12, IF( OR( ISOMITTED( CvtP1Start), ISERROR( CvtP1Start), ISOMITTED( CvtP1End), ISERROR( CvtP1End)), | |
| FALSE, | |
| OR( ROWS( CvtP1Start) <> ROWS( CvtP1End), | |
| COLUMNS( CvtP1Start) <> COLUMNS( CvtP1End))), | |
| DVArg34, IF( OR( ISOMITTED( CvtP2Start), ISERROR( CvtP2Start), ISOMITTED( CvtP2End), ISERROR( CvtP2End)), | |
| FALSE, | |
| OR( ROWS( CvtP2Start) <> ROWS( CvtP2End), | |
| COLUMNS( CvtP2Start) <> COLUMNS( CvtP2End))), | |
| DVArg13, IF( OR( ISOMITTED( CvtP1Start), ISOMITTED( CvtP1End), ISOMITTED( CvtP2Start), ISOMITTED( CvtP2End), | |
| ISERROR( CvtP1Start), ISERROR( CvtP1End), ISERROR( CvtP2Start), ISERROR( CvtP2End), | |
| DVArg12, DVArg34), | |
| FALSE, | |
| OR( AND( COUNTA( CvtP1Start) <> 1, COUNTA( CvtP1Start) <> COUNTA( CvtP2Start)), | |
| AND( COUNTA( CvtP2Start) <> 1, COUNTA( CvtP1Start) <> COUNTA( CvtP2Start)))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3, DVArg4, DVArg12, DVArg34, DVArg13), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Period1Start is missing. Period1Start must be one or more dates (text or numeric) in a row or column."; | |
| "Period1End is missing. Period1End must be teh first date (text or numeric) when something happens."; | |
| "Period2Start is missing. Period2Start must be teh first date (text or numeric) when something happens."; | |
| "Period2End is missing. Period2End must be teh first date (text or numeric) when something happens."; | |
| "Period1Start contains errors in cells. Resolve errors first."; | |
| "Period1End contains errors in cells. Resolve errors first."; | |
| "Period2Start contains errors in cells. Resolve errors first."; | |
| "Period2End contains errors in cells. Resolve errors first."; | |
| "Period1Start must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Period1End must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Period2Start must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Period2End must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Period1Start and Period1End must have same number rows and columns"; | |
| "Period2Start and Period2End must have same number rows and columns"; | |
| "Period1 must have one entry or the same number of entries as Period2, or Period2 must have just 1 entry"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| MinOfEnd, IF(Period2End <= Period1End, Period2End, Period1End), | |
| MaxOfStart, IF(Period2Start >= Period1Start, Period2Start, Period1Start), | |
| Days, MinOfEnd - MaxOfStart + 1, | |
| Result, IF(Days > 0, Days, 0), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| PeriodLabelλ = LAMBDA( | |
| // Parameter Declarations | |
| [Date], | |
| [Interval], | |
| [Diagnostics], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →PeriodLabelλ(Date, [Interval], [Diagnostics])¶" & | |
| "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¶" & | |
| "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: →¶" & | |
| "Result →Formula¶" & | |
| "2023-Feb-26 →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""D"")¶" & | |
| "2023-Feb →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""M"")¶" & | |
| "2023:Q01 →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""Q"")¶" & | |
| "2023 →=PeriodLabelλ(DATEVALUE(""02/26/23""), ""Y"")", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( ISOMITTED( Date)), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtDate, IF( ISOMITTED( Date), | |
| FALSE, | |
| IF( ISNUMBER( Date), Date, DATEVALUE( Date)) | |
| ), | |
| Interval, IF( ISOMITTED( Interval), "M", Interval), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtDate)), | |
| OR( ISERROR( Interval)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( Date), ISERROR( CvtDate)), | |
| FALSE, | |
| OR(CvtDate < 1)), | |
| DVArg2, IF( OR( ISOMITTED( Interval), ISERROR( Interval)), | |
| FALSE, | |
| ISERROR( SEARCH( Interval, "YASQMIWD"))), | |
| DVErrors, VSTACK( DVArg1, DVArg2), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Date is missing. Date must be one or more dates (text or numeric) in a row or column."; | |
| "Date contains errors in cells. Resolve errors first."; | |
| "Interval contains errors in cells. Resolve errors first."; | |
| "Date must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Interval must be one of A or Y (Annually), S (Semi-annually), Q (Quarterly), M (Monthly), I (ISO weekly), W (Weekly), D (Daily)"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // 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! | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| Periodsλ = LAMBDA( | |
| // Parameter Declarations | |
| [Starts], | |
| [Ends], | |
| [Interval], | |
| [Diagnostics], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Periodsλ(Starts, Ends, [Interval], [Diagnostics])¶" & | |
| "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¶" & | |
| "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.¶" & | |
| "Interval →(Optional) Days, Weeks, Months (default), Quarters, or Years (D, W, M, Q, Y)¶" & | |
| "→¶" & | |
| "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "2 →=Periodsλ(""3/31/2023"", ""5/15/2023"")¶" & | |
| "-2 →=Periodsλ(""5/15/2023"", ""3/31/2023"")¶" & | |
| "-12 →=Periodsλ(""1/15/2024"", ""1/16/2023"", , FALSE)¶" & | |
| "-53 →=Periodsλ(""1/15/2024"", ""1/16/2023"", ""W"")", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( Starts), | |
| ISOMITTED( Ends) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtStarts, IF( ISOMITTED( Starts), | |
| FALSE, | |
| IF( ISNUMBER( Starts), Starts, DATEVALUE( Starts)) | |
| ), | |
| CvtEnds, IF( ISOMITTED( Ends), | |
| FALSE, | |
| IF( ISNUMBER( Ends), Ends, DATEVALUE( Ends)) | |
| ), | |
| Interval, IF( ISOMITTED( Interval), "M", Interval), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtStarts)), | |
| OR( ISERROR( CvtEnds)), | |
| OR( ISERROR( Interval)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( Starts), ISERROR( CvtStarts)), | |
| FALSE, | |
| OR(CvtStarts < 1)), | |
| DVArg2, IF( OR( ISOMITTED( Ends), ISERROR( CvtEnds)), | |
| FALSE, | |
| OR(CvtEnds < 1)), | |
| DVArg12, IF( OR( ISOMITTED( Starts), ISERROR( CvtStarts), ISOMITTED( Ends), ISERROR( CvtEnds)), | |
| FALSE, | |
| AND( COUNTA( CvtStarts) <> 1, COUNTA( CvtStarts) <> 1, | |
| OR( ROWS( CvtStarts) <> ROWS( CvtEnds), | |
| COLUMNS( CvtStarts) <> COLUMNS( CvtEnds)))), | |
| DVArg3, IF( OR( ISOMITTED( Interval), ISERROR( Interval)), | |
| FALSE, | |
| ISERROR( SEARCH( Interval, "YQMWD"))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg12, DVArg3), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Starts is missing. Date must be one or more dates (text or numeric) in a row or column."; | |
| "Ends is missing. Date must be one or more dates (text or numeric) in a row or column."; | |
| "Starts contains errors in cells. Resolve errors first."; | |
| "Ends contains errors in cells. Resolve errors first."; | |
| "Interval contains errors in cells. Resolve errors first."; | |
| "Starts must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Ends must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Either Starts or Ends must have one entry or they must have same number rows and columns"; | |
| "Interval must be one of Y (Annually), Q (Quarterly), M (Monthly), W (Weekly), D (Daily)"}, | |
| Messages, FILTER( ErrMsgs, Errors2Show, ""), | |
| // 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)), | |
| Earliest, MIN(DateOne, DateTwo), | |
| Latest, MAX(DateOne, DateTwo) , //+ 1, | |
| Sign, SIGN(Max(DateTwo - DateOne, 1)), | |
| Periods, Switch(Interval, | |
| "D", DATEDIF(Earliest, Latest, "D"), | |
| "W", INT(DATEDIF(Earliest, Latest, "D")/DPW), | |
| "M", DATEDIF(Earliest, Latest, "M"), | |
| "Q", INT(DATEDIF(Earliest, Latest, "M")/MPQ), | |
| "Y", DATEDIF(Earliest, Latest, "Y") | |
| ), | |
| Sign * Periods | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: ScheduleRatesλ | |
| DESCRIPTION:*//**Schedule rates that persist until replaced in a timeline. */ | |
| /* REVISIONS: Date Developer Description | |
| Jun 01 2022 Craig Hatmaker Copyright | |
| Apr 12 2023 Craig Hatmaker Added Help | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| ScheduleRatesλ = LAMBDA( | |
| // Parameter Declarations | |
| [PeriodEnds], | |
| [RateStarts], | |
| [Rates], | |
| [Diagnostics], | |
| // Help | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleRatesλ(PeriodEnds, RateStarts, Rates, [Diagnostics])¶" & | |
| "DESCRIPTION: →Schedules rates that persist until replaced in a timeline from a schedule in a table.¶" & | |
| "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¶" & | |
| "PARAMETERS: →¶" & | |
| "PeriodEnds →(Required) A row of period end dates (timeline).¶" & | |
| "RateStarts →(Required) A column or row of rate effective dates in any order.¶" & | |
| "Rates →(Required) A column or row of rates associated with RateStarts.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "_,10,20,30,30 →=ScheduleRatesλ(Timelineλ(""1/1/2023"",5,""W""),{44931; 44939; 44945}, {10; 20; 30})", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( PeriodEnds), | |
| ISOMITTED( RateStarts), | |
| ISOMITTED( Rates) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtEnds, IF( ISOMITTED( PeriodEnds), | |
| FALSE, | |
| IF( ISNUMBER( PeriodEnds), PeriodEnds, DATEVALUE( PeriodEnds)) | |
| ), | |
| CvtStarts, IF( ISOMITTED( RateStarts), | |
| FALSE, | |
| IF( ISNUMBER( RateStarts), RateStarts, DATEVALUE( RateStarts)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtEnds)), | |
| OR( ISERROR( CvtStarts)), | |
| OR( ISERROR( Rates)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( PeriodEnds), ISERROR( CvtEnds)), | |
| FALSE, | |
| OR(CvtEnds < 1)), | |
| DVArg2, IF( OR( ISOMITTED( RateStarts), ISERROR( CvtStarts)), | |
| FALSE, | |
| OR(CvtStarts < 1)), | |
| DVArg3a, IF( ISOMITTED( Rates), | |
| FALSE, | |
| NOT( OR( ISNUMBER( Rates)))), | |
| DVArg3b, IF( OR( ISOMITTED( RateStarts), ISERROR(CvtStarts), ISOMITTED( Rates)), | |
| FALSE, | |
| OR( ROWS( RateStarts) <> ROWS( Rates), | |
| COLUMNS( RateStarts) <> COLUMNS( Rates))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3a, DVArg3b), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"PeriodEnds is missing. PeriodEnds must be one or more dates (text or numeric) in a row or column in ascending order."; | |
| "RateStarts is missing. RateStarts must be the first date (text or numeric) when a rate is effective."; | |
| "Rates is missing. Rates must be a value to apply starting on its associated RateStarts date."; | |
| "PeriodEnds contains errors in cells. Resolve errors first."; | |
| "RateStarts contains errors in cells. Resolve errors first."; | |
| "Rates contains errors in cells. Resolve errors first."; | |
| "PeriodEnds must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "RateStarts must contain a valid date. Dates can be entered as text dates, or numeric dates."; | |
| "Rates must contain a numeric value."; | |
| "RateStarts and Rates must have same number rows and columns"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Result, XLOOKUP(PeriodEnds, RateStarts, Rates, "", -1), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: ScheduleRatesByItemsλ | |
| DESCRIPTION:*//**Schedule rates that persist until replaced in a timeline for each item in a list. */ | |
| /* REVISIONS: Date Developer Description | |
| Aug 26 2022 Craig Hatmaker Copyright | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| ScheduleRatesByItemsλ = LAMBDA( | |
| // Parameter Declarations | |
| [PeriodEnds], | |
| [ItemFilter], | |
| [ItemList], | |
| [EffectiveList], | |
| [RateList], | |
| [Diagnostics], | |
| [DoNotUse], | |
| // Help | |
| LET( | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleRatesByItemsλ(PeriodEnds, ItemFilter, ItemList, EffectiveList, RateList [Diagnostics])¶" & | |
| "DESCRIPTION: →Schedules each item's rates by period until another rate takes effect.¶" & | |
| "NOTE! →If 2 rates are scheduled within the same period for the same item, .¶" & | |
| "→only the last rate will be selected.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-scheduleratesbyitems%CE%BB¶" & | |
| "PARAMETERS: →¶" & | |
| "PeriodEnds →(Required) A row or column of period end dates (timeline).¶" & | |
| "ItemFilter →(Required) 1 or more items to filter a rate schedule by. ¶" & | |
| " →A 'rate schedule' is a table/list where each row contains a unique item/effective date pair with a rate.¶" & | |
| "ItemList →(Required) Items in a rate schedule.¶" & | |
| "EffectiveList →(Required) Effective dates in the rate schedule.¶" & | |
| "RateList →(Required) Rates in a rate schedule for each item/effective date pair.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "DoNotUse →This is a counter used internally. Do not make any entry.¶" & | |
| "→¶" & | |
| "EXAMPLES: →ScheduleRatesByItemsλ( Timelineλ( ""1/1/2023""), UNIQUE( tblRates(Apartment)), ¶" & | |
| "→tblRates[Apartment], tblRates[Rent], tblRates[Effective])", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( PeriodEnds), | |
| ISOMITTED( ItemFilter), | |
| ISOMITTED( ItemList), | |
| ISOMITTED( EffectiveList), | |
| ISOMITTED( RateList) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| DoNotUse, IF( ISOMITTED( DoNotUse), 1, DoNotUse), | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtPrdEnds, IF( ISOMITTED( PeriodEnds), | |
| FALSE, | |
| IF( ISNUMBER( PeriodEnds), PeriodEnds, DATEVALUE( PeriodEnds)) | |
| ), | |
| CvtEffDates, IF( ISOMITTED( EffectiveList), | |
| FALSE, | |
| IF( ISNUMBER( EffectiveList), EffectiveList, DATEVALUE( EffectiveList)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtPrdEnds)), | |
| OR( ISERROR( ItemFilter)), | |
| OR( ISERROR( ItemList)), | |
| OR( ISERROR( CvtEffDates)), | |
| OR( ISERROR( RateList)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( PeriodEnds), ISERROR( CvtPrdEnds)), | |
| FALSE, | |
| OR(CvtPrdEnds < 1)), | |
| DVArg2, IF( OR( ISOMITTED( ItemFilter), ISERROR( ItemFilter), ISOMITTED( ItemList), ISERROR( ItemList)), | |
| FALSE, | |
| OR( ISNA( MATCH( ItemFilter, ItemList, 0)))), | |
| DVArg3, IF( OR( ISOMITTED( ItemList), ISERROR( ItemList)), | |
| FALSE, | |
| OR( TRIM( ItemList) = "")), | |
| DVArg4, IF( OR( ISOMITTED( EffectiveList), ISERROR( CvtEffDates)), | |
| FALSE, | |
| OR(CvtEffDates < 1)), | |
| DVArg5, IF( OR( ISOMITTED( RateList), ISERROR( RateList)), | |
| FALSE, | |
| OR( Trim( RateList) = "")), | |
| DVArg345, IF( OR( ISOMITTED( ItemList), ISOMITTED( EffectiveList), ISOMITTED( RateList), | |
| ISERROR( ItemList), ISERROR( CvtEffDates), ISERROR( RateList)), | |
| FALSE, | |
| OR( ROWS( ItemList) <> ROWS( CvtEffDates), | |
| ROWS( ItemList) <> ROWS( RateList), | |
| COLUMNS( ItemList) <> COLUMNS( CvtEffDates), | |
| COLUMNS( ItemList) <> COLUMNS( RateList))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3, DVArg4, DVArg5, DVArg345), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Add PeriodEnds argument. PeriodEnds must be one or more dates (text or numeric) in a row or column."; | |
| "Add ItemFilter argument. ItemFilter must one or more items from ItemList."; | |
| "Add ItemList argument. ItemList must be the items from a rate schedule table/list."; | |
| "Add EffectiveList argument. EffectiveList must be the rate effective dates from a rate schedule table/list"; | |
| "Add RateList argument. RateList must be the rates from a rate schedule table/list."; | |
| "Resolve errors in PeriodEnds then try again."; | |
| "Resolve errors in ItemFilter then try again."; | |
| "Resolve errors in ItemList then try again."; | |
| "Resolve errors in EffectiveList then try again."; | |
| "Resolve errors in RateList then try again."; | |
| "Remove invalid dates from PeriodEnds. Dates can be entered as text dates, or numeric dates."; | |
| "Remove items from ItemFilter that are not in ItemList."; | |
| "Remove empty entries from ItemList."; | |
| "Remove invalidates from EffectiveList. Dates can be entered as text dates, or numeric dates."; | |
| "Remove empty entries from RateList."; | |
| "ItemList, EffectiveList, and RateList must have same number rows and columns"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Counts, COUNTA( ItemFilter), | |
| Item, INDEX( ItemFilter, DoNotUse), | |
| ItemDates, FILTER( CvtEffDates, ItemList = Item), | |
| ItemRates, FILTER( RateList, ItemList = Item), | |
| NewRow, XLOOKUP( PeriodEnds, ItemDates, ItemRates, 0, -1), | |
| Result, IF( DoNotUse >= Counts, | |
| NewRow, | |
| VSTACK( | |
| NewRow, | |
| ScheduleRatesByItemsλ( CvtPrdEnds, ItemFilter, ItemList, CvtEffDates, RateList, FALSE, DoNotUse + 1 ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE( Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* 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 | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| ScheduleValuesλ = LAMBDA( | |
| // Parameter Declarations | |
| [PeriodStarts], | |
| [PeriodEnds], | |
| [Values], | |
| [EffectiveDates], | |
| [Diagnostics], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleValuesλ(PeriodStarts, PeriodEnds, Values, EffectiveDates, [Diagnostics])¶" & | |
| "DESCRIPTION: →Place values from a table into a timeline's appropriate period.¶" & | |
| "WEBSITE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-examples/5g-schedulevalues%CE%BB ¶" & | |
| "PARAMETERS: →¶" & | |
| "PeriodStarts →(Required) A column or row of period start dates (timeline).¶" & | |
| "PeriodEnds →(Required) A column or row of period ends dates (timeline).¶" & | |
| "Values →(Required) A column or row of values to be scheduled.¶" & | |
| "EffectiveDates →(Required) A column or row of when a value is scheduled.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES: →=ScheduleValuesλ(Timelineλ(""1/1/23""), Timelineλ(""1/1/23"",,,FALSE), {1;3}, {""1/1/23""; ""2/15/23""})", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( PeriodStarts), | |
| ISOMITTED( PeriodEnds), | |
| ISOMITTED( Values), | |
| ISOMITTED( EffectiveDates) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtPrdStarts, IF( ISOMITTED( PeriodStarts), | |
| FALSE, | |
| IF( ISNUMBER( PeriodStarts), PeriodStarts, DATEVALUE( PeriodStarts)) | |
| ), | |
| CvtPrdEnds, IF( ISOMITTED( PeriodEnds), | |
| FALSE, | |
| IF( ISNUMBER( PeriodEnds), PeriodEnds, DATEVALUE( PeriodEnds)) | |
| ), | |
| CvtEffDates, IF( ISOMITTED( EffectiveDates), | |
| FALSE, | |
| IF( ISNUMBER( EffectiveDates), EffectiveDates, DATEVALUE( EffectiveDates)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtPrdStarts)), | |
| OR( ISERROR( CvtPrdEnds)), | |
| OR( ISERROR( Values)), | |
| OR( ISERROR( CvtEffDates)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( PeriodStarts), ISERROR( CvtPrdStarts)), | |
| FALSE, | |
| OR( CvtPrdStarts < 1)), | |
| DVArg2, IF( OR( ISOMITTED( PeriodEnds), ISERROR( CvtPrdEnds)), | |
| FALSE, | |
| OR( CvtPrdEnds < 1)), | |
| DVArg3, IF( OR( ISOMITTED( Values), ISERROR( Values)), | |
| FALSE, | |
| OR( TRIM( Values) = "")), | |
| DVArg4, IF( OR( ISOMITTED( EffectiveDates), ISERROR( CvtEffDates)), | |
| FALSE, | |
| OR( CvtEffDates < 1)), | |
| DVArg12, IF( OR( ISOMITTED( PeriodStarts), ISOMITTED( PeriodEnds), | |
| ISERROR( CvtPrdStarts), ISERROR( CvtPrdEnds)), | |
| FALSE, | |
| OR( ROWS( CvtPrdStarts) <> ROWS( CvtPrdEnds), | |
| COLUMNS( CvtPrdStarts) <> COLUMNS( CvtPrdEnds))), | |
| DVArg34, IF( OR( ISOMITTED( Values), ISOMITTED( EffectiveDates), | |
| ISERROR( Values), ISERROR( CvtEffDates)), | |
| FALSE, | |
| OR( ROWS( Values) <> ROWS( CvtEffDates), | |
| COLUMNS( Values) <> COLUMNS( CvtEffDates))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3, DVArg4, DVArg12, DVArg34), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Add PeriodStarts argument. PeriodStarts must be one or more dates (text or numeric) in a row or column."; | |
| "Add PeriodEnds argument. PeriodEnds must be one or more dates (text or numeric) in a row or column."; | |
| "Add Values argument. These are the items to be scheduled."; | |
| "Add EffectiveDates argument. EffectiveDates must be the value effective dates."; | |
| "Resolve errors in PeriodStarts then try again."; | |
| "Resolve errors in PeriodEnds then try again."; | |
| "Resolve errors in Values then try again."; | |
| "Resolve errors in EffectiveDates then try again."; | |
| "Remove invalid dates from PeriodStarts. Dates can be entered as text dates, or numeric dates."; | |
| "Remove invalid dates from PeriodEnds. Dates can be entered as text dates, or numeric dates."; | |
| "Remove empty entries from Values."; | |
| "Remove invalid dates from EffectiveDates. Dates can be entered as text dates, or numeric dates."; | |
| "Adjust PeriodStarts and PeriodEnds so they have the same number of rows and columns."; | |
| "Adjust Values and EffectiveDates so they have the same number of rows and columns."}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Result, BYCOL( | |
| SEQUENCE(, COLUMNS(PeriodStarts)), | |
| LAMBDA(Period, | |
| LET(StartDate, INDEX(PeriodStarts, Period), | |
| EndDate, INDEX(PeriodEnds, Period), | |
| SUM(Values * (CvtEffDates >= StartDate) * (CvtEffDates <= EndDate)) | |
| ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: ScheduleValuesByItemsλ | |
| DESCRIPTION:*//**Schedule and sum values in a timeline for each item.*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 28 2022 Craig Hatmaker Copyright | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| ScheduleValuesByItemsλ = LAMBDA( | |
| // Parameter Declarations | |
| [PeriodStarts], | |
| [PeriodEnds], | |
| [ItemFilter], | |
| [ItemList], | |
| [EffectiveList], | |
| [ValueList], | |
| [Diagnostics], | |
| [DoNotUse], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →ScheduleValuesByItemsλ(PeriodStarts, PeriodEnds, ItemFilter, ItemList, EffectiveList, ValueList, [Diagnostics])¶" & | |
| "DESCRIPTION: →Schedule and sum values in a timeline for each item.¶" & | |
| "WEBPAGE: →https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-component-libraries/5g-schedulevaluesbyitem%CE%BB¶" & | |
| "PARAMETERS: →¶" & | |
| "PeriodStarts →(Required) A row of period start dates (timeline).¶" & | |
| "PeriodEnds →(Required) A row of period end dates (timeline).¶" & | |
| "ItemFilter →(Required) A column of 1 or more items to filter a value schedule by. ¶" & | |
| " →A 'value schedule' is a table/list where each row contains a unique item/effective date pair with a value.¶" & | |
| "ItemList →(Required) Items in a value schedule.¶" & | |
| "EffectiveList →(Required) Effective dates in the value schedule.¶" & | |
| "ValueList →(Required) Values in a value schedule for each item/effective date pair.¶" & | |
| "Diagnostics →(Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "DoNotUse →This is a counter used internally. Do not make any entry.¶" & | |
| "→¶" & | |
| "EXAMPLES: →ScheduleRatesByItemsλ( Timelineλ( ""1/1/2023""), Timelineλ( ""1/1/2023"",,,FALSE), ¶" & | |
| "→UNIQUE( tblExp(Items)), tblExp[Items], tblExp[Dates], tblExp[Amount])", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK( | |
| ISOMITTED( PeriodStarts), | |
| ISOMITTED( PeriodEnds), | |
| ISOMITTED( ItemFilter), | |
| ISOMITTED( ItemList), | |
| ISOMITTED( EffectiveList), | |
| ISOMITTED( ValueList) | |
| ), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Counter, IF( ISOMITTED( DoNotUse), 1, DoNotUse), | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| CvtPrdStarts, IF( ISOMITTED( PeriodStarts), | |
| FALSE, | |
| IF( ISNUMBER( PeriodStarts), PeriodStarts, DATEVALUE( PeriodStarts)) | |
| ), | |
| CvtPrdEnds, IF( ISOMITTED( PeriodEnds), | |
| FALSE, | |
| IF( ISNUMBER( PeriodEnds), PeriodEnds, DATEVALUE( PeriodEnds)) | |
| ), | |
| CvtEffDates, IF( ISOMITTED( EffectiveList), | |
| FALSE, | |
| IF( ISNUMBER( EffectiveList), EffectiveList, DATEVALUE( EffectiveList)) | |
| ), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtPrdStarts)), | |
| OR( ISERROR( CvtPrdEnds)), | |
| OR( ISERROR( ItemFilter)), | |
| OR( ISERROR( ItemList)), | |
| OR( ISERROR( CvtEffDates)), | |
| OR( ISERROR( ValueList)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( PeriodStarts), ISERROR( CvtPrdStarts)), | |
| FALSE, | |
| OR(CvtPrdStarts < 1)), | |
| DVArg2, IF( OR( ISOMITTED( PeriodEnds), ISERROR( CvtPrdEnds)), | |
| FALSE, | |
| OR(CvtPrdEnds < 1)), | |
| DVArg3, IF( OR( ISOMITTED( ItemFilter), ISERROR( ItemFilter), ISOMITTED( ItemList), ISERROR( ItemList)), | |
| FALSE, | |
| OR( ISNA( MATCH( ItemFilter, ItemList, 0)))), | |
| DVArg4, IF( OR( ISOMITTED( ItemList), ISERROR( ItemList)), | |
| FALSE, | |
| OR( TRIM( ItemList) = "")), | |
| DVArg5, IF( OR( ISOMITTED( EffectiveList), ISERROR( CvtEffDates)), | |
| FALSE, | |
| OR(CvtEffDates < 1)), | |
| DVArg6, IF( OR( ISOMITTED( ValueList), ISERROR( ValueList)), | |
| FALSE, | |
| OR( Trim( ValueList) = "")), | |
| DVArg12, IF( OR( ISOMITTED( PeriodStarts), ISOMITTED( PeriodEnds), | |
| ISERROR( CvtPrdStarts), ISERROR( CvtPrdEnds)), | |
| FALSE, | |
| OR( ROWS( CvtPrdStarts) <> ROWS( CvtPrdEnds), | |
| COLUMNS( CvtPrdStarts) <> COLUMNS( CvtPrdEnds))), | |
| DVArg456, IF( OR( ISOMITTED( ItemList), ISOMITTED( EffectiveList), ISOMITTED( ValueList), | |
| ISERROR( ItemList), ISERROR( CvtEffDates), ISERROR( ValueList)), | |
| FALSE, | |
| OR( ROWS( ItemList) <> ROWS( CvtEffDates), | |
| ROWS( ItemList) <> ROWS( ValueList), | |
| COLUMNS( ItemList) <> COLUMNS( CvtEffDates), | |
| COLUMNS( ItemList) <> COLUMNS( ValueList))), | |
| DVErrors, VSTACK( DVArg1, DVArg2, DVArg3, DVArg4, DVArg5, DVArg6, DVArg12, DVArg456), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Add PeriodStarts argument. PeriodStarts must be one or more dates (text or numeric) in a rown."; | |
| "Add PeriodEnds argument. PeriodEnds must be one or more dates (text or numeric) in a row."; | |
| "Add ItemFilter argument. ItemFilter must one or more items from ItemList in a column."; | |
| "Add ItemList argument. ItemList must be the items from a rate schedule table/list."; | |
| "Add EffectiveList argument. EffectiveList must be the rate effective dates from a rate schedule table/list"; | |
| "Add ValueList argument. ValueList must be the values from a value schedule table/list."; | |
| "Resolve errors in PeriodStarts then try again."; | |
| "Resolve errors in PeriodEnds then try again."; | |
| "Resolve errors in ItemFilter then try again."; | |
| "Resolve errors in ItemList then try again."; | |
| "Resolve errors in EffectiveList then try again."; | |
| "Resolve errors in ValueList then try again."; | |
| "Remove invalid dates from PeriodStarts. Dates can be entered as text dates, or numeric dates."; | |
| "Remove invalid dates from PeriodEnds. Dates can be entered as text dates, or numeric dates."; | |
| "Remove items from ItemFilter that are not in ItemList."; | |
| "Remove empty entries from ItemList."; | |
| "Remove invalid dates from EffectiveList. Dates can be entered as text dates, or numeric dates."; | |
| "Remove empty entries from ValueList."; | |
| "PeriodStarts and PeriodEnds must have same number rows and columns"; | |
| "ItemList, EffectiveList, and ValueList must have same number rows and columns"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // Procedure | |
| Counts, COUNTA( ItemFilter), | |
| Item, INDEX( ItemFilter, Counter), | |
| ItemDates, FILTER( CvtEffDates, ItemList = Item), | |
| ItemValues, FILTER( ValueList, ItemList = Item), | |
| Array, (ItemDates >= PeriodStarts) * (ItemDates <= PeriodEnds) * ItemValues, | |
| NewRow, BYCOL( Array, LAMBDA( Column, SUM( Column))), | |
| Result, IF( Counter >= Counts, | |
| NewRow, | |
| VSTACK( | |
| NewRow, | |
| ScheduleValuesByItemsλ( CvtPrdStarts, CvtPrdEnds, | |
| ItemFilter, ItemList, CvtEffDates, ValueList, Diagnostics, Counter + 1 ) | |
| ) | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE( Return, Result, Help, Messages) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Timelineλ | |
| DESCRIPTION:*//**Create a horizontal list of period dates*/ | |
| /* REVISIONS: Date Developer Description | |
| Aug 19,2023 Craig Hatmaker Copyright | |
| Sep 23 2023 Craig Hatmaker Added Diagnostics | |
| */ | |
| Timelineλ = LAMBDA( | |
| // Parameter Declarations | |
| [StartDate], | |
| [Periods], | |
| [Interval], | |
| [PeriodStarts?], | |
| [Diagnostics], | |
| LET( | |
| // Help | |
| Help, TRIM(TEXTSPLIT( | |
| "FUNCTION: →Timelineλ(StartDate, Periods, Interval, PeriodStarts?, [Diagnostics])¶" & | |
| "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¶" & | |
| "→¶" & | |
| "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¶" & | |
| "Diagnositcs →(Optional) (Optional) TRUE: Show error messages. Default is FALSE¶" & | |
| "→¶" & | |
| "EXAMPLES: →¶" & | |
| "Result →Formula¶" & | |
| "12 months, starts →=Timelineλ(TODAY())¶" & | |
| "365 Days →=Timelineλ(""1/1/2023"", 365, ""D"")¶" & | |
| "12 quarters, ends →=Timelineλ(TODAY(), , ""Q"", FALSE)", | |
| "→", "¶" | |
| ) | |
| ), | |
| // Named Constants | |
| MPY, 12, //Months Per Year | |
| MPQ, 3, //Months Per Quarter | |
| DPW, 7, //Days Per Week | |
| // Check inputs - Omitted required arguments | |
| OmittedArgs, VSTACK(ISOMITTED( StartDate)), | |
| Help?, AND( OmittedArgs), | |
| // Check inputs - Set optional arguments' defaults and apply convenience conversions | |
| Diagnostics, IF( ISLOGICAL( Diagnostics), Diagnostics, FALSE), | |
| 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), | |
| // Check inputs - Errors in argument values | |
| ErrorsInArgs, VSTACK( | |
| OR( ISERROR( CvtStart)), | |
| OR( ISERROR( Periods)), | |
| OR( ISERROR( Interval)) | |
| ), | |
| // Check inputs - Data validation errors | |
| DVArg1, IF( OR( ISOMITTED( StartDate), ISERROR( CvtStart)), | |
| FALSE, | |
| OR(CvtStart < 1)), | |
| DVArg2a, IF( OR( ISOMITTED( Periods), ISERROR( Periods)), | |
| FALSE, | |
| NOT( ISNUMBER( Periods))), | |
| DVArg2b, IF( OR( DVArg2a, ISERROR( Periods), ISOMITTED( Periods)), | |
| FALSE, | |
| OR( Periods <1)), | |
| DVArg3, IF( OR( ISOMITTED( Interval), ISERROR( Interval)), | |
| FALSE, | |
| OR( ISERR(SEARCH( Interval, "DWMQY")))), | |
| DVErrors, VSTACK( DVArg1, DVArg2a, DVArg2b, DVArg3), | |
| // Any input errors detected? | |
| AnyErrors?, OR(OmittedArgs, ErrorsInArgs, DVErrors), | |
| // Assemble Error Messages Array (if errors found) | |
| Errors2Show, VSTACK( OmittedArgs, Diagnostics * ErrorsInArgs, Diagnostics * DVErrors), | |
| ErrMsgs, {"Add StartDate argument. StartDate must be a single date (text or numeric)."; | |
| "Resolve errors in StartDate then try again."; | |
| "Resolve errors in Periods then try again."; | |
| "Resolve errors in Interval then try again."; | |
| "StartDate is an invalid date. Dates can be entered as text dates, or numeric dates."; | |
| "Periods must be numeric."; | |
| "Periods must be a positive whole number"; | |
| "Interval must be one of D (daily), W (weekly), M (monthly), Q (quarterly) or Y (annually)"}, | |
| Messages, FILTER(ErrMsgs, Errors2Show, ""), | |
| // 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! | |
| ), | |
| // Handle Error | |
| Return, IF( Help?, 2, IF( OR( Errors2Show), 3, IF( AnyErrors?, #VALUE!, 1))), | |
| // Return Result | |
| CHOOSE(Return, Result, Help, Messages) | |
| ) | |
| ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment