Results 1 to 7 of 7

Thread: Using a Formula in Excel to create an "x" if date entered is between two dates

  1. #1

    Using a Formula in Excel to create an "x" if date entered is between two dates



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

    I am looking for a way to create a formula that will give me an "x" in a cell if one date falls between a certain quarter and the second date falls between another quarter.

    The quarters are as follows:
    Jan-Feb-Mar
    April-May-June
    July-Aug-Sept
    Oct-Nov-Dec

    For example: If the first date I enter is 2/13/13 and the second date I enter is in 5/24/13 which is the following quarter, I want an "X" to appear in a cell labeled "positive." If the first date is still 2/13/13 and the second date is 6/24/13 which is not in the following quarter, I want an "X" to appear in a cell labeled "negative."

    Below is what I want it to look like:

    Date Entered Returned to Work Positive Negative
    2/13/2013 5/24/2013 X
    2/13/2013 6/24/2013 X


    Right now all I can get it to look like is this:

    Date Entered Returned to Work Positive Negative
    2/13/2013 5/24/2013 2 0
    2/13/2013 6/24/2013 1 1


    the formula that I have, for the table above is: =COUNTIFS(A2:B2,">=1/01/13",A2:B2,"<=5/30/13")

    Any recommendations?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try, in C2:

    =IF(ROUNDUP(MONTH(B2)/3,0)-1<=ROUNDUP(MONTH(A2)/3,0),"X","")

    copied down

    then in D2:

    =IF(C2="X","","X")

    copied down

    Note: I think your second comparison is wrong, June date is in 2nd quarter which is one quarter after February quarter... maybe you meant July/24/2013


  3. #3
    You are right, I did mean July. I am going to see if it works. Thank you!


    Quote Originally Posted by NBVC View Post
    Try, in C2:

    =IF(ROUNDUP(MONTH(B2)/3,0)-1<=ROUNDUP(MONTH(A2)/3,0),"X","")

    copied down

    then in D2:

    =IF(C2="X","","X")

    copied down

    Note: I think your second comparison is wrong, June date is in 2nd quarter which is one quarter after February quarter... maybe you meant July/24/2013

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Let us know... for sake of completion and for the benefit of future searchers with same problem....


  5. #5

    Mistake in Dates - See Edits

    Made a mistake in one of the dates. See edits below. June should have been July.

    - Erika

    Quote Originally Posted by Erika View Post
    I am looking for a way to create a formula that will give me an "x" in a cell if one date falls between a certain quarter and the second date falls between another quarter.

    The quarters are as follows:
    Jan-Feb-Mar
    April-May-June
    July-Aug-Sept
    Oct-Nov-Dec

    For example: If the first date I enter is 2/13/13 and the second date I enter is in 5/24/13 which is the following quarter, I want an "X" to appear in a cell labeled "positive." If the first date is still 2/13/13 and the second date is 6/24/13 which is not in the following quarter, I want an "X" to appear in a cell labeled "negative."

    Below is what I want it to look like:

    Date Entered Returned to Work Positive Negative
    2/13/2013 5/24/2013 X
    2/13/2013 7/24/2013 X


    Right now all I can get it to look like is this:

    Date Entered Returned to Work Positive Negative
    2/13/2013 5/24/2013 2
    2/13/2013 7/24/2013 1 1


    the formula that I have, for the table above is: =COUNTIFS(A2:B2,">=1/01/13",A2:B2,"<=5/30/13")

    Any recommendations?

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Sorry... i meant let us know if the solution indeed works...


  7. #7
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    120
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    Crossposted:

    ExcelForum

    ExcelGuru

Posting Permissions

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