Return a value if a cell date is between 2 dates

X M
X M ✭✭✭✭
in Formulas and Functions

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

Tags:
· Share on FacebookShare on Twitter

Best Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    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: ramzi@cedartreeconsulting.com

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

    · Share on FacebookShare on Twitter
  • X M
    X M ✭✭✭✭
    Answer ✓

    Thanks for your help Ramzi.

    · Share on FacebookShare on Twitter

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    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: ramzi@cedartreeconsulting.com

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

    · Share on FacebookShare on Twitter
  • X M
    X M ✭✭✭✭
    Answer ✓

    Thanks for your help Ramzi.

    · Share on FacebookShare on Twitter
  • Ramzi K
    Ramzi K ✭✭✭✭✭

    @xav

    You bet! Anytime.

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

    Feel free to email me: ramzi@cedartreeconsulting.com

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!