How to check if a date is between 2 date fields

1

I have a statutory vacation dates table. and I also have a vacations/leave calendar table.
I’d like to know if there is a way to check if a statutory day is in the Start/end date of the vacations/leave calendar.
example:
Statutory Day - New Years : Jan 1st 2021
Vacation Request: December 28th 2020 to Jan 3rd 2021
I’d like to check if , and display in a column, if New Years is in the vacation requests date range,
so I can also exclude the Stat day from vacation days calculation

2

Dear @Mohamed_Hamad1 ,

I have the feeling that this post is the direction to go

Credit to @Pch

3

You can use NetWorkingDays() formula for calculating vacation days.

If there is the table ‘StatutoryDayTable’ which contains statutory days on column ‘date’,
you can use the following formula

NetWorkingDays(vacationStartDate, vacationEndDate, StatutoryDayTable.date)

The limitation of this formula is that it excludes all weekends(Saturday and Sunday). So in the situation that it needs to include Saturday or Sunday as working days, you can’t use this formula.

4

This is a pretty old post but I think I would add a solution. You can sequence() the start and end dates and look to see if a particular date lives between it

e.g.

Sequence(VacationStarteDate, VacationEndDate).contains(NewYearsDate)

1 Like
5

FYI here’s the most optimal solution for finding date overlaps:

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.