Minimum Date in a range of dates that is >= Today?

ZCP
ZCP ✭
edited 12/15/22 in Formulas and Functions

I need a formula that returns the minimum date from a range of dates that is greater than or equal to today's date.

I use the MIN function to return the minimum date in the range but, I need the minimum date that is greater than or equal to today's date. Today's date needs to be automatically generated so I have been using Today () in the formula.

Thank you!

Tags:

Best Answer

  • ZCP
    ZCP ✭
    Answer βœ“

    I got this to work. Thank you for your help Dale! I really appreciate it.

    πŸ˜€

    =MIN((COLLECT([Eff. Date]2:[Cond. Prec. Close]2, [Eff. Date]2:[Cond. Prec. Close]2, >=TODAY())))


Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @ZCP Try putting a collect function inside the min function:

    =MIN((COLLECT([Date 1]:[Date 1], [Date 1]:[Date 1], >TODAY())))

    That should let you find the minimum of dates that ONLY meet the collect criterion of being greater than today.

    dm

  • ZCP
    ZCP ✭

    Thank you for your answer. I really just want the one next minimum date. Kind of like the next due date from the range of dates. These dates are populated from different project due dates onto a master sheet that i can look at to see what is coming up next. So, some projects will have 4 or 5 dates in the future. I'm just interested in the next or closest date that is coming due. Then, i can sort the sheet by this column and prioritize which projects have something coming due the quickest.

    I just have one range of dates, which covers 7 or 8 columns. Below is the range of dates I have. Do I need to put each column in separately instead of trying to use a range? This formula gave me an Incorrect error.

    MIN((COLLECT([Eff. Date]2:[Cond. Prec. Close]2, >TODAY())))

  • ZCP
    ZCP ✭
    Answer βœ“

    I got this to work. Thank you for your help Dale! I really appreciate it.

    πŸ˜€

    =MIN((COLLECT([Eff. Date]2:[Cond. Prec. Close]2, [Eff. Date]2:[Cond. Prec. Close]2, >=TODAY())))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!