# Trouble creating formula to find lowest date

1. ## Trouble creating formula to find lowest date

I am having trouble creating a formula for these dates. My goal is to create something that will compare all the dates listed. It should do so by taking dates 1-5 and adding 5 years to them, and adding 6 months to "date 6 months" then it should display the lowest of these values in expiration date. It has been giving me a lot of issues and I'm also not sure how to make it so it ignores cells that are empty, as excel assumes the date is 1900 when the cell is blank.  Register To Reply

2. ## Re: Trouble creating formula to find lowest date

Try this:

=MIN(EDATE(MIN(A6:E6),60),EDATE(F6,6))  Register To Reply

3. ## Re: Trouble creating formula to find lowest date

In G6, array-enter (enter using Ctrl-Shift-Enter) the formula

=MIN(IF(A6:E6<>"",DATE(YEAR(A6:E6)+5,MONTH(A6:E6),DAY(A6:E6))),IF(F6>0,EDATE(F6,6)))

or this, suggested by Ali's post

=MIN(EDATE(MIN(IF(A6:E6<>"",A6:E6)),60),IF(F6>0,EDATE(F6,6)))  Register To Reply

4. ## Re: Trouble creating formula to find lowest date

That's a smart usage of EDATE. I tried it with the 5 cells and it didn't like that... but if one is blank.....  Register To Reply

5. ## Re: Trouble creating formula to find lowest date

I tried implementing the formula, but I guess I tried to dumb down my setup too much when I posted initially. Can you try doing the same for this new setup?  Register To Reply

6. ## Re: Trouble creating formula to find lowest date

In N5, array-enter with Ctrl-Shift-Enter

=MIN(EDATE(MIN(IF(ISNUMBER(B5:L5),B5:L5)),60),IF(ISNUMBER(M5),EDATE(M5,6)))  Register To Reply

7. ## Re: Trouble creating formula to find lowest date

When the Date + 6 months is blank, the expiration date defaults to 1/0/1900, any idea how to fix that?  Register To Reply

8. ## Re: Trouble creating formula to find lowest date

I didn't try that one - I thought the others might be blanks...

Anyway, array-enter this - if you have no dates at all it will return a blank, and it should handle various missing dates better:

=IF(COUNT(B5:M5)=0,"",MIN(IF(COUNT(B5:L5)=0,100000,EDATE(MIN(IF(ISNUMBER(B5:L5),B5:L5)),60)),IF(M5>0,EDATE(M5,6),100000)))  Register To Reply

9. ## Re: Trouble creating formula to find lowest date

Thanks, Bernie! This works wonders, I can't tell you how long I tried messing around with it!  Register To Reply

10. ## Re: Trouble creating formula to find lowest date

Well done, Bernie - and thank you for the compliment. It made contributing to the thread worthwhile.   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 