Trying to compare two dates and print the most recent plus seven years

[Phily50]

I'm getting myself in all sorts of a muddle with this formula.

I have two dates - one in cell D8 and one in cell E8. I want to find the most recent date and then add seven years to it which will then put that new date into cell J8. The reason I have used the isblank and iserror is because there is blanks (obvs!) and also some cells contain n/k for not known.

I have adapted this formula from one that was working before that only looked at one date - it was working - but now I have added E8 it has to compare the two rather than just look at one.

I dont really want it to show 01/01/1900 if one of the cells is blank or says n/k

=IF(ISBLANK(DATE(YEAR(MAX(E8,D8))+ 7)), MONTH(MAX(E8,D8), DAY(MAX(E8,D8))),"",IFERROR(DATE(YEAR(MAX(E8,D8D8)))) + 7, MONTH(MAX(E8,D8(D8), DAY(MAX(E8,D8(D8)),""))))

Grateful of any help from you guys as always.

 

[awoohaw]

NOt sure what you mean about blanks, do you mean blanks in E8 or D8?

Book4
	D	E	F
7	Date1	Date2	Max + 7
8	5/13/2022	8/15/2022	8/15/2029
9
Sheet1

Cell Formulas
Range	Formula
F8	F8	=EDATE(MAX(D8:E8),7*12)

 

[awoohaw]

Here it is testing n/k or blanks in some date cells.

Book4
	D	E	F
7	Date1	Date2	Max + 7
8	5/13/2022	dkajlkd	5/13/2029
9	5/18/2021	3/6/2020	5/18/2028
10	12/18/2022		12/18/2029
11	n/k	5/19/2020	5/19/2027
12	8/30/2020	3/5/2021	3/5/2028
13	12/6/2022	5/25/2020	12/6/2029
Sheet1

Cell Formulas
Range	Formula
F8:F13	F8	=EDATE(MAX(D8:E8),7*12)

 

[Phily50]

NOt sure what you mean about blanks, do you mean blanks in E8 or D8?

Book4
	D	E	F
7	Date1	Date2	Max + 7
8	5/13/2022	8/15/2022	8/15/2029
9
Sheet1

Cell Formulas
Range	Formula
F8	F8	=EDATE(MAX(D8:E8),7*12)

That formula works, however by blanks I mean just as you say - Blanks or n/k in E8 or D8. If theres only a n/k in one of the cells its fine, but if its in both (which some of the cells further down the sheet do have, it returns "31/12/1906". I'd rather it be blank or at the very least, 'N/K'

 

[awoohaw]

try.
=IF(and(e8=0,d8=0),"",EDATE(MAX(D8:E8),7*12),""))

 

[Phily50]

=IFERROR(EDATE(MAX(D8:E8),7*12),"")

Thanks, but I still get the same default date (31/12/1906) if the cells E8 & D* contain both blank or both N/K.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Phily50]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for the tip!

My version is Microsoft® Excel® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20648) 32-bit

I have updated my profile to reflect this.

 

[awoohaw]

Try checking to see if they are numbers:
=IF(AND(ISNUMBER(E8)=FALSE,ISNUMBER(E8)=FALSE),"",
EDATE(MAX(D8:E8),7*12),""))

 

[Fluff]

I have updated my profile to reflect this.

Thanks for that.
How about

=LET(m,MAX(D8:E8),IF(m=0,"",EDATE(m,7*12)))