Hello all. I have been working o a formula to set a date, based on a couple of conditions, for a few days now without any success. I have tried multiple IF statements but I think I am close with an IF AND statement. However, my resulting date turns out to be Jan 01, 1900 so I have some issues.
Here is what I am trying to do. I have a membership list. Assume row 193 for all statements. In column H I have a date that shows the members current expiry date. Based on that, column L uses an IF statement to set either "Expired" if that date in column H is older than today or "Active" if that date is in the future. (Note I also have color coding in column L based on conditional formatting. All expired cells are red and all Active cells are green.)
I have added columns I and J for when a member renews their subscription. Column I is the date they renew (Y/M/D) and column J is the renewal interval in years (1, 2, etc.)
When a member comes along to renew, if they are renewing before their current membership has expired column L will show "Active" with a green cell color. I want the formula I am working on to look at column L to see if it shows "Active" and column J to see if a new renewal interval has been input. If both conditions are true I then want the NEW expiry date, shown in column H, to be the date already shown in column H plus one year.
If however,
a member comes along to renew and their current membership has previously expired I want the formula to look at column L to see if it shows "Expired" and column J to see if a new renewal interval has been input. If both these conditions are true I then want the NEW expiry date, in column H, to be the date entered in column I plus one year (assuming the renewal interval was one year).
Hope this makes sense.
Here is the IF AND formula I am placing in cell H193 (Note that this user IS EXPIRED already):
=IF(AND(LI193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))
When I use this formula the date in Column H is returned as January 1, 1900, no matter whether column L is Active or Expired.
Any guidance would be appreciated. If I am off track please tell me so.
Thanks for any help.
Doug
]]>