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

Angela.R

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.

Sameer Karkhanis

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)

Sameer Karkhanis

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

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

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))