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.
Last edited by DaBestEva; 11-20-2020 at 04:48 PM.
Try this:
=MIN(EDATE(MIN(A6:E6),60),EDATE(F6,6))
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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)))
Last edited by Bernie Deitrick; 11-20-2020 at 01:05 PM.
Bernie Deitrick
Excel MVP 2000-2010
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.....
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?
In N5, array-enter with Ctrl-Shift-Enter
=MIN(EDATE(MIN(IF(ISNUMBER(B5:L5),B5:L5)),60),IF(ISNUMBER(M5),EDATE(M5,6)))
When the Date + 6 months is blank, the expiration date defaults to 1/0/1900, any idea how to fix that?
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)))
Thanks, Bernie! This works wonders, I can't tell you how long I tried messing around with it!
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)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks