# INDEX/MATCH Formula

Options
edited 12/09/19

Hello!

I'm creating a document where I would like to index a column of dates, and return a specific date from that list. I need one of two things to occur:

1) If a date occurs in the upcoming week, I would like to return the latest date of the week

2) If no date occurs in the upcoming week, I would like to return the minimum value of all dates greater than the upcoming Friday.

I've included a screenshot, and below is a description of what I would like to do:

My issue is in getting Row 3, Column[-] to return the correct date. My current formula is, "=MAX(COLLECT([-]7:[-]25, @cell < [1]7))". That way, when I have two dates (10/22 & 10/24) in the upcoming week, it will show the maximum value. My issue is that when I am on the week of 10/28, the returned date will be the highest value less than 11/4, which would still be my 10/24 deadline, even though it would have already occurred. I need, in the event that there are no deadlines between 10/28-11/4, to show the minimum date beyond 11/4, in this case, 11/13.

A heads up that the table within the black border auto updates from week to week. On Tuesday, the current cell saying 10/21 will change to the following Monday, and all cells below will update by one week.

Looking for help with a formula that can return a max of next week, or minimum or a range beyond next week!

Thanks!

Grant

• Employee
Options

Hello,

Happy to help. From my understanding, you'd like to produce a formula that returns the nearest date within the next 7 days. However, if there is no date in the next 7 days produce the nearest date after that. If this is the desired goal you can achieve this utilize a formula similar to the below to achieve this:

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

The above formula will always produce the next most relevant date.

Note: You'll want to replace the values "Date Column Name" with the name of your date columns.

Have a wonderful day.

Cheers,

Eric

Smartsheet Technical Support

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!