Return a value if a cell date is between 2 dates

X M

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

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

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.