Return a value if a cell date is between 2 dates

Hi,

I want to automate one of my sheet summary by retrieving the Sprint# based on today's date. (When today's date is between the beginning and end of a sprint, I want the sprint# to be retrieved in the Automated Sprint field of the summary sheet).

I have tried to use =INDEX([Sprint#]:[Sprint#], MATCH([Sprint start date]:[Sprint start date] <= [Today's date]:[Today's date]) * ([Sprint end date]:[Sprint end date] >= [Today's date]:[Today's date], 0)) but it get #UNPARSEABLE

Screen Shot 2020-10-18 at 00.31.15.png

Any help will be greatly appreciated

Thanks

Xav..

Find more posts tagged with

Formulas

Accepted answers

Ramzi K

@xav

Try this instead

=MAX(COLLECT([Sprint#]:[Sprint#], [Sprint Start Date]:[Sprint Start Date], <=TODAY(), [Sprint End Date]:[Sprint End Date], >=TODAY()))

You also don't really need the Today's Date column if you use this formula.

I Hope that helps you.

Cheers,

Ramzi

X M

Thanks for your help Ramzi.

All comments

Ramzi K

@xav

Try this instead

=MAX(COLLECT([Sprint#]:[Sprint#], [Sprint Start Date]:[Sprint Start Date], <=TODAY(), [Sprint End Date]:[Sprint End Date], >=TODAY()))

You also don't really need the Today's Date column if you use this formula.

I Hope that helps you.

Cheers,

Ramzi

X M

Thanks for your help Ramzi.

Ramzi K

@xav

You bet! Anytime.

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.