I have an excel spreadsheet that uses a variety of formulas and returns the dates that certain events will happen - actually, dates that I need to do certain things. The spreadsheet is in no order and can’t be sorted to show the dates sequentially. I'm looking for some kind of function or lookup command that tells excel to scan the worksheet containing the dates and return the value of the next date on the list. For example - if the next date somewhere on the list is Friday and today is Monday, I want excel to give me the Friday date so I know I have an action to take on Friday. The worksheet is large and scanning the rows and columns by eye looking for the next date is just too difficult. Hope this makes sense. Thanks. Lisa
2 Answers
I suggest using an array formula to return the earliest date that is today's date or later. I have set Column A to be your list of dates (adjust the number of rows to match your data). Enter the following in a cell, and enter it as an array formula by pressing Ctrl+Shift+Enter.
=MIN(IF($A$1:$A$13-TODAY()>=0,$A$1:$A$13))
When entered as an array formula, the formula will appear in set brackets, like this:
{=MIN(IF($A$1:$A$13-TODAY()>=0,$A$1:$A$13))}
I hope this helps.
I went about this a little differently, no arrays needed Find how many numbers are bigger then the one you are looking for with CountIf() Then I used =Large this will find the nth dates in a list we have the nth we are looking for in the countIF()
=LARGE(A:A,COUNTIF(A:A,">="&TODAY()))
