Count public holidays between two dates

[Anna_Pedersen]

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

[Steve · Accepted solution]

I would think this would work:

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

[Anna_Pedersen]

Steve:

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

Hi Steve

Thanks very much! I was so close

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])
)
)
)