checking dates in two sheets

jap

In smartsheet one sheet called Holidays have 3 colum

Name start_date end_date

jap 03/March/2023 05/March/2023

ALex 01/March/2023 02/March/2023

jap 19/March/2023 25/March/2023

other smart sheet called as class List having 4 colum

Class CourseDate TeacherName Conflict

math 02/March/2023 ALex False

English 20/March/2023 jap True

so if the teacher Name in class list exist in Hlidays list check if the course Date of Class List doest fall between the start_date and end_ate of the Holiday list and if so Conflict should be True

Kelly Moore

Hey @jap

To clarify, is the answer for ALex ‘false’? It is falling on the end_date.

Assuming that your ALex example above should be “True”, try this

=IF(COUNTIFS({Test Sheet Names}, TeacherName@row, {Test Sheet start_date}, [Course Date]@row >= @cell, {Test Sheet end_date}, @cell >= [Course Date]@row) > 0, "True", "False")

If you want to exclude the start and end dates, so that “False” is the desired outcome in the ALex example above, then remove the equal signs in the formula above.

=IF(COUNTIFS({Test Sheet Names}, TeacherName@row, {Test Sheet start_date}, [Course Date]@row > @cell, {Test Sheet end_date}, @cell > [Course Date]@row) > 0, "True", "False")

Will either of these work for you?
Kelly