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 < 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!
Help Article Resources
Check out the Formula Handbook template!