checking dates in two sheets

Options
jap
jap ✭✭
edited 10/09/23 in Formulas and Functions

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

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!