Formula to Determine NEXT DATE from a group of Dates

Susan Morash

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()))

ericncarr

@Susan Morash

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.

Paul Newcome

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.

ericncarr

https://community.smartsheet.com/discussion/comment/343413#Comment_343413

You're welcome!

ericncarr

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.

Susan Morash

Thanks for the quick response. Unfortunately we can't have all of the dates in one column, each date signifies something different.

Paul Newcome

Are each of those columns next to each other or are there other columns in between that need to be excluded?

Susan Morash

They're not next to each other but I can do that if it's required for the Collect function?

ericncarr

@Susan Morash

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.

Susan Morash

It worked!!! Thanks so much!!

Paul Newcome

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.

ericncarr

https://community.smartsheet.com/discussion/comment/343413#Comment_343413

You're welcome!