CODEWITHSUNDEEP
excel
K20GH
K20GH

Reputation: 6281

Check if any date within a range is within 1 month of todays date

I've got a column full of dates. How can I check that column to find which date is within a month of todays date, and then return it?

If there is no date within a month, just return blank

Lets say my dates are:

01-Jan-12   
01-Apr-12   
01-Jul-12   
01-Oct-12   
01-Jan-13

The code im using is below. A:A is the range of the dates above

=MIN(IF(A:A>TODAY(),A:A))

The issue im having is that if I use the above, it returns 01/01/12 and not 01/01/13. Also, if I change the dates so the next date is December 1st 2012, it still returns 01/01/12

Upvotes: 0

Views: 9346

Answers (1)

barry houdini
barry houdini

Reputation: 46371

So you really just want the earliest date if that's within a month? If so perhaps try

=IF(MIN(A:A)-TODAY()<=30,MIN(A:A),"")

Assumes dates in column A

If you have past and future dates try this formula

=IFERROR(SMALL(IF(A2:A100>=TODAY(),IF(A2:A100<=TODAY()+30,A2:A100)),1),"")

confirmed with CTRL+SHIFT+ENTER

or for exactly 1 month (rather than 30 days) try using EDATE, i.e.

=IFERROR(SMALL(IF(A2:A100>=TODAY(),IF(A2:A100<=EDATE(TODAY(),1),A2:A100)),1),"")

Upvotes: 2

Related Questions