Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.