# countif excel between two dates

Discussion in 'Business Applications' started by apickrell, Aug 19, 2008.

Not open for further replies.
1. ### apickrellThread Starter

Joined:
Aug 19, 2008
Messages:
5
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.

2. ### WendyMRetired Trusted Advisor

Joined:
Jun 27, 2003
Messages:
4,042
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.

File size:
13.5 KB
Views:
1,829
3. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
You could use a shorter version of Wendy's formula:

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

(returns FALSE/TRUE)

4. ### WendyMRetired Trusted Advisor

Joined:
Jun 27, 2003
Messages:
4,042
Uh, yeah, or you could do that. Duh. I KNEW I made it overly complicated! Thank you, sir.

5. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Or, if you'd prefer to avoid the helper column:

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

6. ### WendyMRetired Trusted Advisor

Joined:
Jun 27, 2003
Messages:
4,042
Well now you're just showing off.

7. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Hey, we've ALL done it!

8. ### apickrellThread Starter

Joined:
Aug 19, 2008
Messages:
5
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.

9. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
"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).

10. ### apickrellThread Starter

Joined:
Aug 19, 2008
Messages:
5
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.

11. ### WendyMRetired Trusted Advisor

Joined:
Jun 27, 2003
Messages:
4,042
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!

12. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
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

File size:
14.5 KB
Views:
823
13. ### apickrellThread Starter

Joined:
Aug 19, 2008
Messages:
5
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.

14. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
That's what the formula in E7 of the latest attachment does; result = TRUE. Did you check it?

15. ### apickrellThread Starter

Joined:
Aug 19, 2008
Messages:
5
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.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!