How do I count specific days within a range?

I'm trying to figure out how to count a list of holidays in a date range. So I have a date range of 2022-01-14 (start date in one column) to 2022-03-01 (end date in another column). If i have a list of holidays like below in I want a count of how many times one of those dates falls within the range. So in this example I would have an output of 2 from 2022-01-14 to 2022-03-01 because 2 holiday dates fall within that range.

HOLIDAY_DT
1. 2022-01-17 00:00:00
2. 2022-02-21 00:00:00
3. 2022-05-30 00:00:00
4. 2022-06-20 00:00:00
5. 2022-07-04 00:00:00
6. 2022-09-05 00:00:00
7. 2022-11-11 00:00:00
8. 2022-11-24 00:00:00

Tips and Tricks

Date Time

Help

Accepted answers

Luke_C

Hi @ArmandoGuerra

I would suggest:

Append the holidays to the ranges
Filter on holidays that fall between the ranges
Summarize/count the matches

New Workflow5.yxmd

All comments

Luke_C

Hi @ArmandoGuerra

I would suggest:

Append the holidays to the ranges
Filter on holidays that fall between the ranges
Summarize/count the matches

New Workflow5.yxmd

ChrisTX

Use the Generate Rows tool. See attached workflow.

Chris

How do I count specific days within a range.yxmd

rfoster7

There's a lot of ways to do this: There is an advanced join non-standard tool you can use. Or you can generate rows to generate one row per day between your start and end date, then do a join and then recondense it. Or you can just do the equivalent of a cartegian join with the holiday table and then a filter followed by a group by to recondense it. Probably the generate rows tool is the prettiest, so I'll show you that one.

example of generate rows to create dates for holiday count.yxmd

ArmandoGuerra

Luke_C thank for the quick solve

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703