Return true of Date equals a date within a range of specified dates

helper column

=IF([Deploy Date]@row = [Deploy Date]29, 1, 0)

Works fine. If the date in the row equals the date in row 29 - returns 1

However i'd like it to search within a range of dates and i'm clearly missing something.

=IF([Deploy Date]@row = [Deploy Date]28:[Deploy Date]39, 1, 0))

I'd like if the deploy date @ row equals ANY date within a range of dates listed on row 28-39 - return 1.

That way conditional format will look for the 1, highlight row red.


Best Answer


  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓

    You cannot compare or equate a value directly with a range. Try the below,

    =IF(MATCH([Deploy Date]@row, [Deploy Date]28:[Deploy Date]39, 0) > 0, 1, 0)
  • Angela.R
    Angela.R ✭✭✭

    Thank you! How do I tell it to ignore e cell Thank you, How do i tell it to ignore if the cell @row is blank or if the range of dates. [Deploy date] 28:[Deploy Date]39 are blank?

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    Try this: (basically this check if both the cell at row and the range is not empty. Range is assumed not empty if at least one column within the range has value)

    =IF(AND(NOT(ISBLANK([Deploy Date]@row)),
    	COUNTIF([Deploy Date]28:[Deploy Date]39, <>"") > 0
    	), IF(MATCH([Deploy Date]@row, [Deploy Date]28:[Deploy Date]39, 0) > 0, 1, 0))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!