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

1. ## 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")

Any recommendations?

2. 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. You are right, I did mean July. I am going to see if it works. Thank you!

Originally Posted by NBVC
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. Let us know... for sake of completion and for the benefit of future searchers with same problem....

5. ## Mistake in Dates - See Edits

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

- Erika

Originally Posted by Erika
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. Sorry... i meant let us know if the solution indeed works...

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