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

- Erika

2. ## 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

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

Originally Posted by ErikaCBU
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. ## 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","")

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

Crossposted:

ExcelForum

ExcelGuru

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

Touche, Martin

11. ## 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.

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

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

#### 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