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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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
 

Erika

New member
Joined
Jul 23, 2013
Messages
3
Reaction score
0
Points
0
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
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Let us know... for sake of completion and for the benefit of future searchers with same problem....
 

Erika

New member
Joined
Jul 23, 2013
Messages
3
Reaction score
0
Points
0
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?
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Sorry... i meant let us know if the solution indeed works... :)
 
Top