INDEX/MATCH Formula

hildegrant

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: 

I would like Column[Deadline] under current deadline to match whatever deadline in the all deadlines matches the returned date in Column[-]. I have this figured out already. 

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

Eric M Oliveira

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