LAMBDA: IsOccuranceDateλ
Description
Returns a an array of TRUE/FALSE values where TRUE indicates an event, that can potentially repeat, occurs on a specified date.
Use this when we need to schedule events, payments or revenues that may recur. If we leverage Excel's handling of TRUE and FALSE, where TRUE equates to 1 and FALSE equates to 0, we can then multiply IsOccuranceDateλ's results by an event's amount to place each amount under the date in which it occurs.
Syntax
We see this LAMBDA being use in the GIF.:
=IsOccuranceDateλ( <Dates>, <StartEvent>, <EndEvent> <Repeats> )
Where:
Dates - is a one dimensional array of dates by day from the earliest start date to the last event date.
FirstOccurence is a one dimensional array (or table column) of when an event starts
EndOccurences (optional) is a one dimensional array (or table column) of when an event ends (if ever)
Repeats is a code designating the interval upon which the event recurs. Repeat codes are:
O=One time
A=Annually
S=Semi-Annually
Q=Quarterly
B=BiWeekly
W=Weekly
X=Every Workday (USA normal)
D=Daily
Code
This is the code as of this writing. This is a good example of how to use SWITCH and LET. For the most current version, see the Gist.
/*
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.
EXAMPLE: IsOccurrenceDateλ(Dates, tblItems[Start], tblItems[End], tblItems[Recurrence]) * tblItems[Amount]
For an Example see: https://www.dropbox.com/s/ddthth8dy9l9ug4/BudgetPQL.xlsx?dl=1
ARGS:
Date Date to test. Normally this is horizontal array of dates
FirstOccurfence This is when something that may repeat, happens for the first time
EndOccurences After this date, recurrences do not happen
Repeats This is a code describing how often something repeats
O=One time
A=Annually
S=Semi-Annually
Q=Quarterly
B=BiWeekly
W=Weekly
X=Every Workday (USA normal)
D=Daily
Copyright ©: Craig Hatmaker 2022 - Anyone may use but not claim ownership or authorship
*/
IsOccuranceDateλ = LAMBDA(
// Parameter declarations
Date,
FirstOccurence,
EndOccurences,
Repeats,
// Function starts here
LET(
// Period in Months
SemiAnnually, 6,
Quarterly, 3,
// Period in Days
Biweekly, 14,
Weekly, 7,
// Months/Year, Months/Quarter, Days/Week
MPY, 12,
MPQ, 3,
DPW, 7,
// WEEKDAY parameters
Mon2Sun, 2,
Friday, 5,
SameDay, DAY(Date) = DAY(FirstOccurence),
// Function starts here
(FirstOccurence <= Date) *
(ISBLANK(EndOccurences) + (Date <= EndOccurences)) *
SWITCH(LEFT(Repeats, 1),
"O", Date = FirstOccurence,
"A", TEXT(Date, "MMDD") = TEXT(FirstOccurence, "MMDD"),
"S", SameDay * (MOD(MONTH(Date), SemiAnnually) = MOD(MONTH(FirstOccurence), SemiAnnually)),
"Q", SameDay * (MOD(MONTH(Date), Quarterly) = MOD(MONTH(FirstOccurence), Quarterly)),
"M", SameDay,
"B", MOD(Date, Biweekly) = MOD(FirstOccurence, Biweekly),
"W", MOD(Date, Weekly) = MOD(FirstOccurence, Weekly),
"X", WEEKDAY(Date, Mon2Sun) <= Friday,
"D", TRUE,
0
) ) = 1
);
Importing
Use the Advance Formula Environment add-in available (free) from the Microsoft store. Click Advanced Formula Environment to learn more about that.
All of my LAMBDAs can be found here: https://gist.github.com/CHatmaker.
This specific LAMBDA is part of a Dates library at this URL:
https://gist.github.com/CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b
