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.

Find more posts tagged with

Formulas

Accepted answers

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)

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.