Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: IF AND statement

  1. #1
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365

    IF AND statement



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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))


  3. #3
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365
    Quote Originally Posted by NBVC View Post
    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))
    Thanks for picking up on that. Unfortunately it made no difference.

    D.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Seems to work when I use my own sample data.. can you post sample workbook showing the problem?


  5. #5
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365
    Quote Originally Posted by NBVC View Post
    Seems to work when I use my own sample data.. can you post sample workbook showing the problem?
    I wil do some testing and then post a sample

  6. #6
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365
    Quote Originally Posted by NBVC View Post
    Seems to work when I use my own sample data.. can you post sample workbook showing the problem?
    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.
    Attached Files Attached Files

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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.


  8. #8
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365
    Quote Originally Posted by NBVC View Post
    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.
    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?

    =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.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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().


  10. #10
    Seeker Dougj's Avatar
    Join Date
    Oct 2018
    Posts
    8
    Articles
    0
    Excel Version
    2007 office365
    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.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •