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

Erika

New member
Joined
Jul 23, 2013
Messages
3
Reaction score
0
Points
0
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 EnteredReturned to WorkPositiveNegative
2/13/20135/24/2013X
2/13/20136/24/2013X


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

Date EnteredReturned to WorkPositiveNegative
2/13/20135/24/201320
2/13/20136/24/201311


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

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


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
 
Let us know... for sake of completion and for the benefit of future searchers with same problem....
 
Mistake in Dates - See Edits

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

- 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?
 
Sorry... i meant let us know if the solution indeed works... :)
 
Back
Top