Formula to Determine NEXT DATE from a group of Dates
I Have a series of Go Live Date columns in a Smartsheet and I want a new column called "Next Date" that determines which of the dates is the next Go Live date. I using the following formula but it doesn't return any value:
=MIN(COLLECT([Release 1 Go Live]@row, [Release 2 Go Live]@row, [Release 3 Go Live]@row, [Release 4 Go Live]@row, [TA Go Live Date]@row, [PCS Go Live Date]@row, [TM Go Live Date]@row, [TF Go Live Date]@row, [JIE Go Live Date]@row, [WFEX Go Live Date]@row, >=TODAY()))
Best Answers
-
If this is something you're going to be using long term I'd recommend the 3 columns approach I put in above for ease of use but if not then the original method works.
Formula:
=MIN(COLLECT([Release 1 Go Live]@row:[Release 3 Go Live]@row, [Release 1 Go Live]@row:[Release 3 Go Live]@row, @cell > TODAY() - 1))
It does a collect on the three columns to gather any dates who are greater than Today - 1, so if the Release Date is today it will show as the Next Date (if you don't want that, just remove the - 1 after Today() in the formula), then gives the minimum date of those collected.
Both options side by side.
-
I was going to suggest having them next to each other. If you wanted to keep the visible structure of the sheet, you can insert a hidden helper column for each of the Go Live dates and use cell references to pull in the date from the working portion of the sheet then reference the helper columns in your formula.
-
You're welcome!
Answers
-
Personally, I would have all the Go-live dates in one column and the label in another, so "Release" and "Go Live Date." Then I'd have a third column for "Next GL Date" or something like that with a formula:
=[Go Live Date]@row - Today()
This checks how many days there are for a given release between that date and today's date.
Then in your Next Go Live column you would put this formula in one of your cells:
=INDEX(COLLECT([Go Live Date]:[Go Live Date], [Next GL Date]:[Next GL Date], =MIN(COLLECT([Next GL Date]:[Next GL Date], [Next GL Date]:[Next GL Date], >-1))), 1)
Basically it collects the Go Live Dates and grabs the one whose Next GL Date value is smallest (soonest) but is greater than -1.
-
Thanks for the quick response. Unfortunately we can't have all of the dates in one column, each date signifies something different.
-
Are each of those columns next to each other or are there other columns in between that need to be excluded?
-
They're not next to each other but I can do that if it's required for the Collect function?
-
If this is something you're going to be using long term I'd recommend the 3 columns approach I put in above for ease of use but if not then the original method works.
Formula:
=MIN(COLLECT([Release 1 Go Live]@row:[Release 3 Go Live]@row, [Release 1 Go Live]@row:[Release 3 Go Live]@row, @cell > TODAY() - 1))
It does a collect on the three columns to gather any dates who are greater than Today - 1, so if the Release Date is today it will show as the Next Date (if you don't want that, just remove the - 1 after Today() in the formula), then gives the minimum date of those collected.
Both options side by side.
-
It worked!!! Thanks so much!!
-
I was going to suggest having them next to each other. If you wanted to keep the visible structure of the sheet, you can insert a hidden helper column for each of the Go Live dates and use cell references to pull in the date from the working portion of the sheet then reference the helper columns in your formula.
-
You're welcome!
Help Article Resources
Categories
Check out the Formula Handbook template!
