Results 1 to 4 of 4

Thread: Challenging If/And statement help please
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Challenging If/And statement help please

    Good day!

    I hope someone can assist me with the following problem

    I am trying to get a return value of Yes/No indicating if the customer is active for the current month based on the following (which are all dates)

    Example 1
    Contract Start Date: 01/01/2014
    Contract End Date: *blank*
    Current Month: 09/01/2019

    In this case, the customer the customer is active for 9/2019.

    Example 2
    Contract Start Date: 03/01/2016
    Contract End Date: 08/31/2019
    Current Month: 09/01/2019

    In this case, the customer the customer is NOT active for 9/2019.

    Can someone please assist. I am really frustrated. Thanks

  2. #2
    Board Regular ChrisGT7's Avatar
    Join Date
    Jul 2012
    Location
    Athens
    Posts
    87
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Challenging If/And statement help please

    Hi Munnah,

    I assume that B1 = Start Date, B2 = End Date, B3 = Current Month.

    In any other cell, type the following formula:
    =IF(AND(B3>=IF(NOT(ISNUMBER(B1)),DATEVALUE("1/1/1900"),B1),B3<=IF(NOT(ISNUMBER(B2)),DATEVALUE("12/31/9999"),B2)),"Active","Inactive")
    Your curiosity will be the death of you!

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,731
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Challenging If/And statement help please

    Welcome to the Board!

    If your dates are in cells A1, A2, and A3, try this formula:
    Code:
    =IF(OR(AND(A1<=A3,A2=""),AND(A1<=A3,A2>=A3)),"Yes","No")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Challenging If/And statement help please

    Welcome to the forum!

    Try this


     ABCDEFG
    1ContractCustomerStart DateEnd Date CustomerActive
    21damor01/01/2014  damorYes
    32Mun03/01/201630/08/2019 MunNo

    CellFormula
    G2=IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))="","Yes",IF(INDEX(D2:D5,MATCH(F2,B2:B5,0))>=EOMONTH(TODAY(),0),"Yes","No"))
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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