# countif excel between two dates



## apickrell (Aug 19, 2008)

I am having trouble figuring out how to write the correct function using countif. I have two columns. One column has the start date and the other column has the end date. I want a separate cell to countif those two dates are between the specified dates.

start date end date
jane doe 1/22/2008 4/3/2008

February ?

So for February I need to countif those two dates fall between 2/1/2008 and 2/28/2008.
Please help.


----------



## WendyM (Jun 28, 2003)

Hi, welcome to TSG. 

There's probably a cleaner way to do this and I'm sure someone will take a look at it soon, but see my attached spreadsheet for what I did. Basically, I created an extra column that says "yes" if the condition is true, and "no" if it's not. My If statement looks at the start date and determines whether it's earlier than 2/1/08. If so, the answer is no. If not, it looks at the start date AND the end date to determine whether they're both earlier than 2/28/08. If so, the answer is yes. Note: this only works if you assume the end date is always going to be the same day or later than the start date. The Countif function in C11 just counts the number of yeses. You could put that function in another column and hide the yes/no column if you needed to. You could also hide the rows that specify 2/1/08 and 2/28/08, but if you're looking for different date ranges at different times, it could come in handy. Let me know if that didn't make any sense.  Like I said, I'm sure someone will have a cleaner answer soon.


----------



## bomb #21 (Jul 1, 2005)

You could use a shorter version of Wendy's formula:

*=AND(A2>=$B$11,B2<=$B$12)*

(returns FALSE/TRUE)


----------



## WendyM (Jun 28, 2003)

Uh, yeah, or you could do that.  Duh. I KNEW I made it overly complicated! Thank you, sir. :up:


----------



## bomb #21 (Jul 1, 2005)

Or, if you'd prefer to avoid the helper column:

=SUMPRODUCT((A2:A6>=B11)*(B2:B6<=B12))


----------



## WendyM (Jun 28, 2003)

Well now you're just showing off.


----------



## bomb #21 (Jul 1, 2005)

Wendy said:


> ... I KNEW I made it overly complicated! ...


Hey, we've ALL done it!


----------



## apickrell (Aug 19, 2008)

this helps but maybe I did not explain myself well because the first set of dates should have been yes also. basically these are start and end dates of contracts and I want to know below where it states February, if the above dates overlap the dates in February.


----------



## bomb #21 (Jul 1, 2005)

"Overlap" is ambiguous.

From your first post:

"I want a separate cell to count(if) those two dates are *between* the specified dates."

If the month is February, that makes the specified dates 1st Feb 2008 and 29th Feb 2008. Since neither 22nd Jan nor 3rd April are between 1st Feb and 29th Feb, I'm afraid you've lost me (and anyone else, possibly).


----------



## apickrell (Aug 19, 2008)

any february date does fall between jan 22 and april 4th. I need the start and end date to read as a range of dates. which in turn february dates again would fall in that range.


----------



## WendyM (Jun 28, 2003)

So you need to know whether your specified date range falls between the start and end dates? Sorry, that's the opposite of what you said in your first post, so you basically just need the opposite of Andy's formula. But I'll let him say for sure, since who knows how complicated I might manage to make it!


----------



## bomb #21 (Jul 1, 2005)

Wendy said:


> So you need to know whether your specified date range falls between the start and end dates? Sorry, that's the opposite of what you said in your first post ...


Agreed.

apickrell, see the "Qualified v2" formulas in E:E and the SUMPRODUCT "variation" in C12 of the attached (an update of Wendy's "template"). HTH


----------



## apickrell (Aug 19, 2008)

I think that I am really confusing you on how I state this. Let me try again. so from Feb 1 to Feb 29, if any of those dates or between those dates falls anywhere between the start and end date it needs to count as one. So if you have 1/22/2008 to 4/2/2008 as your start and end date then the answer would be yes because any date in feb would fall between those two dates. Does this help any.


----------



## bomb #21 (Jul 1, 2005)

That's what the formula in E7 of the latest attachment does; result = TRUE. Did you check it?


----------



## apickrell (Aug 19, 2008)

I see but the answer for b12 should be 3 because 3 of those above start and end dates have a feb date in their range.


----------



## slurpee55 (Oct 20, 2004)

There are not 3 but 4 date ranges that have some values in Feb. in the example sheet
01/15/08	02/20/08
02/15/08	02/20/08
02/07/08	02/28/08
01/22/08	04/03/08
However, there are just 2 that fall fully in Feb. see attached


----------



## bomb #21 (Jul 1, 2005)

slurpee said:


> There are not 3 but 4 date ranges that have some values in Feb. in the example sheet ...


Agreed, & I (now) believe that that 4-count is what the OP wants.

I'm now thinking 3-way IF:

1. If Start Date month/year = "Target", count

2. If Start Date < Target (start), if Start Date + (Start Date/End Date DATEDIF) >= Target (start), count

3. If Start Date > Target (end), don't count.

Sound likely/logical?


----------



## slurpee55 (Oct 20, 2004)

Ya know, this is one of those times we need Father Ted to guide us....  - 'cause I sure got confused reading this thread.


----------



## bomb #21 (Jul 1, 2005)

Try:

=IF(A2>$B$12,0,IF(DATE(YEAR(A2),MONTH(A2),1)=DATE(YEAR($B$11),MONTH($B$11),1),1,IF(A2<$B$11,IF(B2>=$B$11,1,0))))

in D2 & copied down. That's:

=IF(A2>$B$12,0,
IF(DATE(YEAR(A2),MONTH(A2),1)=DATE(YEAR($B$11),MONTH($B$11),1),1,
IF(A2<$B$11,IF(B2>=$B$11,1,0))))


----------



## WendyM (Jun 28, 2003)

See, told ya it was complicated.


----------



## bomb #21 (Jul 1, 2005)

Quiet, you.


----------

