# 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.

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

Try this:

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

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)))

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.....

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?

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)))

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?

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)))

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!

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.

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