INDEX/MATCH Formula Help
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
Comments
-
Take your initial MAX formula. Use that in an IF statement to say that if it is a date, use it, otherwise use the MIN of date beyond 7 days.
.
So to get the MAX you have:
=MAX(COLLECT([-]7:[-]25, @cell < [1]7))
To get the MIN of dates beyond next week, just flip your @cell < [1]7 to @cell >= [1]7 and change your function from MAX to MIN.
MIN(COLLECT([-]7:[-]25, @cell >= [1]7))
.
Now we can use both of these to populate an IF statement.
=IF(ISDATE(MAX(COLLECT(..........))), MAX(COLLECT(..........)), MIN(COLLECT(..........)))
-
Paul,
In that situation, the max value "< [1]7" would still be the 10/24 date, and still be returned. I think I somehow need to indicate that if its not within 5 days of that specified date, to show the min value.
I'm getting a "#UNPARSABLE" when I use the formula you listed above.
Thanks!
Grant
-
Can you post the exact formula that gave you the unpareseable error?
-
I've tweaked this one a little bit, but think its on the right track. This is the formula I have now, and I'm not having luck with it..
"=IF(COUNTIF([-]7:[-]25, AND(@cell >= $[1]$6, @cell < $[1]$7)) = 0, MIN(COLLECT([-]7:[-]25, [-]7:[-]25, @cell >= [1]7))), MAX(COLLECT([-]7:[-]25, [-]7:[-]25, @cell < [1]7))"
I'm basically rearranging to say that if there are no dates next week, to show the minimum value beyond next week, but if there are some, to show the highest value in the upcoming week. Any idea why this one would be coming back #UNPARSABLE"?
Thanks!
Grant
-
Hi Paul,
I actually went with your formula again, and it worked out, so I must have mistyped something the first time. However, it is giving me the old date like I thought. I changed some of the dates in my range to better show you what I'd like to do.
Basically, since there are no values between 10/28-11/04, I would like to return the 11/5 date in my range. However, the formula written will still return 10/20. The dates will not be deleted after they are past, which is what I think breaks your formula. This is going to be a master list of all dates, past, current, and future.
Thanks for the help!
Grant
-
Perfect! Thats what I needed! Thank you!!!
-
Ah. Ok. I must have misread your original post when I saw that the list of dates was ever changing.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 141 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 489 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!