Check if a Date is between a Calendar Event

intelligence
(Fabian Weller) #1

I have a calendar view where we can add Holidays.
How can I check if [Date] is between one of our Holidays?

So in this example if [Date] = 03/20/2019 , then it’s TRUE.
But if [Date] = 03/15/2019 , then it’s FALSE.

It seems to me that this is a feature request for Calendar Functions. Ideally, one would want to express this as:

IF(INCLUDES(EVENT(“Holiday”), [Date]), TRUE, FALSE)

or alternatively,

IF(AND(EVENT_START(“Holiday”)> [Date], EVENT_END(“Holiday”)< [Date]), TRUE, FALSE)

Screenshot_4

(Aleksi Alkio) #2

Do you mean that you don’t have that holiday (3/18…3/23) in your table as a record?

(Fabian Weller) #3

I do have that holiday in my table. I added it via the calendar view.
In another table I want to check if [Date] is in between the holiday.

(Aleksi Alkio) #4

So… you have calendar table where that holiday is added like start and end date. Now you would like to check if the date in another table is inside of your holiday or holidays?

(Fabian Weller) #5

Yes.

(Aleksi Alkio) #6

Then you can check it like…
COUNT(SELECT(Table[KeyCol],
AND(
[_THISROW].[Date]>=[StartDate],
[_THISROW].[Date]<=[EndDate])
))=0

2 Likes
(Fabian Weller) #7

Excellent. Thank you.

(Reza Raoofi) closed #8