Return a value if a cell date is between 2 dates

10/18/20
Accepted

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

Any help will be greatly appreciated

Thanks

Xav..

Popular Tags:

Best Answers

  • Ramzi KRamzi K ✭✭✭
    Accepted Answer

    @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

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • xavxav
    Accepted Answer

    Thanks for your help Ramzi.

Answers

  • Ramzi KRamzi K ✭✭✭

    @xav

    You bet! Anytime.

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

Sign In or Register to comment.