Minimum Date in a range of dates that is >= Today?
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!
Best 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
-
@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
-
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())))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!