Check if date is in vacation time

[Beneindias]

Hi guys,

Trying to check if a date is between a range of dates in a table with date ranges.

So, I have a table with two columns, col C is first day of vacation and col D is last day.

And I'm trying to come up with a function to tell me if a given date is inside that range. I'm toying with countif, but I'm not getting the expected results.

The function that I came up is:

=IF(AND(COUNTIF(Tabela2[Início];"<"&CONCATENATE(B9;$AA$2;$AE$2));COUNTIF(Tabela2[Fim];">"&CONCATENATE(B9;$AA$2;$AE$2)));"VACATION";"WORK")

Row B has the days
AA2 have the month
AE2 have the year

My thinking to this function was, if that date is bigger than "Inicio" and smaller than "Fim", it's vacation time, but, as there are a few "ranges" of dates, the result is not right.

Right now, if I use "1-8-2022" it tells me that its vacation time, because its considering "18-4-2022" as smaller and "31-8-2022" and "31-12-2022" as bigger dates.
How can I do this? I know how to do it if I have single dates, but I was trying with date ranges to make this easier when updating the file.

Thanks to all

 

[Beneindias]

Found the solution. Probably not the best way to do it, but I'm getting the expected values.

Had to scrap that function and used Index match instead.

This is the function that worked

=IF(AND(INDEX(Tabela2[Início];(MATCHTemplate!B10;Tabela2[Início];1));1)<=Template!B10;INDEX(Tabela2[Fim];(MATCH(Template!B10;Tabela2[Início];1));1)>=B10);"Vacation";"Work")