Edate based on 3 date variables

[davids4500]

Hi everybody..
I have an excel spreadsheet as attached.
three columns have dates A1 which is todays date, H3 is the second date and J3 is the third date.
I need a formula that applies these 3 criteria:- If J3 = 0, and H3 = zero then P3 =EDATE(A1, O3), but if J3 = 0 and H3 has a date then P3 = EDATE(H3, O3), but if J3 has a date (and H3 may also have a date) then P3 =EDATE(J3, O3)

 

[Phuoc]

Try this:

=IF(J3>0,EDATE(J3,O3),IF(H3>0,EDATE(H3,O3),EDATE(A1,O3)))

 

[davids4500]

Try this:

=IF(J3>0,EDATE(J3,O3),IF(H3>0,EDATE(H3,O3),EDATE(A1,O3)))

Thank you Phuoc...it works...however, if O3 has a zero, it gives a #VALUE! answer. How do i fix that?

 

[bebo021999]

This return black cell if O3=0 or blank

=IFERROR(EDATE(IF(J3>0,J3,IF(H3>0,H3,A1)),O3),"")

 

[Alex Blakenburg]

Thank you Phuoc...it works...however, if O3 has a zero, it gives a #VALUE! answer. How do i fix that?

0 (or even blank) in O3 can't cause that error message. Do you have a formula in column O ? Does that formula return "" under certain conditions (show us the formula).
What do you want to happen if it returns "" do you want it to use it as if it was zero ?
Ideally change the formula in O3 to return 0 not "" if that is what you want.
Or you could try something like this:

=LET(mths,N(O3),
     IF(J3>0,EDATE(J3,mths),IF(H3=0,EDATE($A$1,mths),EDATE(H3,mths))))

If that is not what you want bebo's formula is another option.

 

[davids4500]

This return black cell if O3=0 or blank

=IFERROR(EDATE(IF(J3>0,J3,IF(H3>0,H3,A1)),O3),"")

thank you so much. i changed the "" to $A$1 and works perfectly.

 

[davids4500]

0 (or even blank) in O3 can't cause that error message. Do you have a formula in column O ? Does that formula return "" under certain conditions (show us the formula).
What do you want to happen if it returns "" do you want it to use it as if it was zero ?
Ideally change the formula in O3 to return 0 not "" if that is what you want.
Or you could try something like this:

=LET(mths,N(O3),
     IF(J3>0,EDATE(J3,mths),IF(H3=0,EDATE($A$1,mths),EDATE(H3,mths))))

If that is not what you want bebo's formula is another option.

0 (or even blank) in O3 can't cause that error message. Do you have a formula in column O ? Does that formula return "" under certain conditions (show us the formula).
What do you want to happen if it returns "" do you want it to use it as if it was zero ?
Ideally change the formula in O3 to return 0 not "" if that is what you want.
Or you could try something like this:

=LET(mths,N(O3),
     IF(J3>0,EDATE(J3,mths),IF(H3=0,EDATE($A$1,mths),EDATE(H3,mths))))

If that is not what you want bebo's formula is another option.

 

[davids4500]

Hi Alex. Thak you for the formula. I have noticed that though the variables are the same, the result was different for several instances eg(for line 37 & 38) and lines (92-95). Can you please explain that. I checked to make sure the cells were formatted the same etc. Look forward to your reply. David

 

[Alex Blakenburg]

What happens is you change the formula to this:

=LET(mths,ROUND(N(O3),0),
     IF(J3>0,EDATE(J3,mths),IF(H3=0,EDATE($A$1,mths),EDATE(H3,mths))))

 

[davids4500]

=LET(mths,ROUND(N(O3),0), IF(J3>0,EDATE(J3,mths),IF(H3=0,EDATE($A$1,mths),EDATE(H3,mths))))

Yes Alex...that has fixed it...much appreciated and thank you for sharing your knowledge and time....simply amazed