+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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")

    Let me know if you need more information.

    - Erika

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

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

    Hi Erika,

    I used a comparison of VLOOKUPs... see attached...

    Please let me know if this works for you and if you have any questions...

    Dennis
    Attached Files Attached Files

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

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

    Quote Originally Posted by ErikaCBU View Post
    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."
    This part really throws me off.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

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

    =IF(CEILING(MONTH($A1)/3,1)=CEILING(MONTH($B1)/3,1),"x","") and
    =IF(CEILING(MONTH($A1)/3,1)<>CEILING(MONTH($B1)/3,1),"x","")
    Attached Files Attached Files
    Last edited by martindwilson; 07-23-2013 at 01:46 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

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

    Martin,

    Sorry, I think I might have found a little flaw in your formula... try 2/1/2013 and 1/1/2014... they are within the same quarter, but different years... and your formula if showing "positive"

  6. #6
    Registered User
    Join Date
    07-23-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    I made a mistake in my explaination.June should have been July. Also, the format changed when I posted the information and moved the columns.

    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

    Basically, the first date "Date Entered" notifies what quarter they are in (Q1,Q2,Q3,Q4). If the second date "Returned to Work" happens in the following quarter from first date "Date Entered," I want an "X" to appear under the cell I labeled "Positive." If the second date "Returned to Work" happens in the another quarter besides the following quarter, I want an "X" to appear in the cell I labeled "Negative."

    I know it is a bit confusing, but much simpler to describe in person. We want to show that if someone returns to work in the following quarter, it is positive and if they don't return to work in the following quarter, it is negative. We want to do this by just entering the two dates and have Excel tell if it is negative or positive.

    I am going to try out the formulas submitted. Thank you for all of your input!

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

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

    You never mentioned years in your original post so no flaw there then. so why is june ,july? but you can add the year
    easier still sinceif one is x the other isn't
    so in c1
    =IF(AND(CEILING(MONTH(A1)/3,1)=CEILING(MONTH(B1)/3,1),YEAR(A1)=YEAR(B1)),"x","") for positives
    in d1 =IF(c1="","x","")
    Last edited by martindwilson; 07-23-2013 at 07:57 PM.

  8. #8
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

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

    Crossposted:

    ExcelForum

    ExcelGuru
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  9. #9
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

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

    Touche, Martin

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

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

    see reply #7

  11. #11
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    Erika, When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

    (link above in the menu bar)


    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create a Command Button to "Save As" and "Close" an Excel Workbook
    By thedunna in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-26-2013, 05:38 PM
  2. Using "match" with dates - dates entered with vba return error
    By Islwyn in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-30-2012, 09:47 AM
  3. [SOLVED] create links to check boxes marked "good" fair"and "bad"
    By pjb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-19-2006, 09:25 PM
  4. Find a "date" in a column of dates in Excel 2000
    By JR Hester in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2005, 06:20 PM
  5. Replies: 2
    Last Post: 09-29-2005, 01:52 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1