Count public holidays between two dates

Hi There,

I am trying to count the number of days between two dates (start, end) on a form that appear in another table (public holidays). I have tried a number of options including Select/Filter etc, but cannot get it right! the public holidays table is not sequential and has roughly 11 dates per year in it.

I could create a table with all dates and flag the holiday ones but hoped there was a elegant formula to do this

Cheers

I would think this would work:

(
  [end]
  - [start]
  - COUNT(
    FILTER(
      "public holidays",
      AND(
        ([date] >= [_THISROW].[start]),
        ([date] <= [_THISROW].[end])
      )
    )
  )
)
1 Like

Hi Steve

Thanks very much! I was so close :slight_smile:

I modified it as i only needed the count section (it helps me in a calc of fees)

COUNT(
FILTER(
“public holidays”,
AND(
([date] >= [_THISROW].[start]),
([date] <= [_THISROW].[end])
)
)
)

1 Like