IF AND statement

D

Dougj

New member
Joined
Oct 29, 2018
Messages
22
Reaction score
0
Points
1
Excel Version(s)
2007 office365
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
​
 
The formula above is referencing column LI not column L. Could that be the issue?

=IF(AND(LI193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))

should be maybe:

=IF(AND(L193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))
 
NBVC said:
The formula above is referencing column LI not column L. Could that be the issue?

=IF(AND(LI193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))

should be maybe:

=IF(AND(L193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))
Click to expand...

Thanks for picking up on that. Unfortunately it made no difference.

D.
 
Seems to work when I use my own sample data.. can you post sample workbook showing the problem?
 
NBVC said:
Seems to work when I use my own sample data.. can you post sample workbook showing the problem?
Click to expand...

I wil do some testing and then post a sample
 
NBVC said:
Seems to work when I use my own sample data.. can you post sample workbook showing the problem?
Click to expand...

See the attached sample sheet. Notice cell D7 is where I have input the new formula (refereed to as H193 in my previous posts). Before I entered data in cells E7,F7 & G7 I updated the formula in D7 to the If AND statement. As soon as I entered that formula in D7 it complained about a circular reference and set the date in D7 to Jan 01 1900. Subsequently entering data in cells E,F & G7 made no change to D7.

As a reminder my intent is to update cell D7 based on whether cell H7 shows Active or Expired.
 

Attachments

  • Test_Formula Column D.xlsx
    12.8 KB · Views: 12
You are getting circular reference because the formula in column D is depending on column H and the formula in column H is depending on column D.

You might have to relook at your formula in column H and what the parameters should be to come with expected results there.
 
NBVC said:
You are getting circular reference because the formula in column D is depending on column H and the formula in column H is depending on column D.

You might have to relook at your formula in column H and what the parameters should be to come with expected results there.
Click to expand...

I am struggling to figure out how to change the date in a cell in column D without a circular reference. I tried these two alterations where I do not refer to cell H. Instead I test if the date already in cell D is older than today or in other words expired. However, every time I do it I get the circular reference popup and the date in cell D always goes to Jan 01 1900?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(AND(L193="Expired",J193<>""),EDATE(I193,12*J193),EDATE(H193,12*J193))
=IF(AND(H193<=TODAY(),ISNUMBER(J193)),EDATE(I193,12*J193),EDATE(H193,12*J193))

D.
[/FONT]
 
In column H you are checking column D to determine if the result should be "Expired", then in column D, you base the result on if H is "Expired". That won't work. Why don't you change the last part of the formula - In the file you attached earlier; EDATE(D3,12*F3) - If you have nothing in column E or F, then what is the next expiration date based on? If you want it to be today simply use TODAY().
 
OK lets try this a different way.
Lets say I have a cell A1 that contains a members current expiry date. Lets assume, as an example, that the date in cell A1 is a month in the future.

Next lets assume that the member in question knows that his membership will expire in a month so he decides to pay his dues today. Lets assume that the membership coordinator then inputs todays date in cell B1 (since this is the day the member is renewing) and in cell C1 an entry of 1 (for a one year renewal or 2 for a two year renewal, etc) is entered.

So my question becomes as follows.

How do I create a formula in A1 that looks at the date in A1 and recognizes that the date is in the future (still active), then recognizes entries in B1 and C1 and then recalculates the date in A1 to the date that was already in A1 PLUS one year?

On the other hand if the date in A1 is todays date or a date in the past (membership has expired or is expiring today), then looks at B1 and C1 and recalculates the date in A1 to todays date PLUS one year?

Every tiem I try this I get the circular reference error.

D.
​
 
I think you will need to use VBA to do this as you require.

Add a button in the sheet that will add the number of years to the sheet.

Something like this will add to the row that has a cell selected...

Code: 
Sub Refresh()

If Cells(ActiveCell.Row, 2).Value <> "" And Cells(ActiveCell.Row, 3).Value <> "" Then
    If Cells(ActiveCell.Row, 1).Value > Date Then
        Cells(ActiveCell.Row, 1).Value = DateAdd("yyyy", Cells(ActiveCell.Row, 3), Cells(ActiveCell.Row, 1).Value)
    Else
        Cells(ActiveCell.Row, 1).Value = DateAdd("yyyy", 1, Date)
    End If
End If

End Sub
 
You must log in or register to reply here.
Back
Top