Skip to content

Instantly share code, notes, and snippets.

@1504168
Created October 16, 2022 19:30
Show Gist options
  • Save 1504168/76dbce7d20b72c90fb9c570f96c2480e to your computer and use it in GitHub Desktop.
Save 1504168/76dbce7d20b72c90fb9c570f96c2480e to your computer and use it in GitHub Desktop.
VALIDQUARTERFINDER = LAMBDA(QuarterData,
LET(
FindStartDate, LAMBDA(QuarterText,
DATE(MID(QuarterText, 2, 4), RIGHT(QuarterText, 1) * 3 - 2, 1)
),
FindQuarterText, LAMBDA(GivenDate,
"Y" & YEAR(GivenDate) & "Q" & ROUNDUP(MONTH(GivenDate) / 3, 0)
),
QuarterBetweenTwoDate, LAMBDA(QuarterData, Input2,
LET(
StartDate, FindStartDate(QuarterData),
EndDate, EOMONTH(FindStartDate(Input2), -3),
Result, IFERROR(
UNIQUE(
FindQuarterText(
SEQUENCE(EndDate - StartDate + 1, 1, StartDate)
)
),
""
),
Result
)
),
QuarterList, REDUCE(
"",
SEQUENCE(ROWS(QuarterData)),
LAMBDA(AllQuarter, CurrentRowIndex,
VSTACK(
AllQuarter,
QuarterBetweenTwoDate(
INDEX(QuarterData, CurrentRowIndex, 1),
INDEX(QuarterData, CurrentRowIndex, 2)
)
)
)
),
ValidQuarterList, FILTER(QuarterList, QuarterList <> ""),
Quarter, UNIQUE(ValidQuarterList),
Count, MAP(
Quarter,
LAMBDA(CurrentQuarter,
ROWS(
FILTER(ValidQuarterList, ValidQuarterList = CurrentQuarter)
)
)
),
Result, VSTACK({"Quarter", "Count"}, HSTACK(Quarter, Count)),
Result
)
);
@1504168
Copy link
Author

1504168 commented Oct 16, 2022

Invocation : =VALIDQUARTERFINDER(B3:C9)

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