Help Please calculating date range - Other posts didn't help

I

Iona

Hello,

I really need help troubleshooting a formula. I am in Excel 2003.
I am trying to count a column that has a different dates within a specific
date range as well as to include the first and end of month dates.
Column Name (Column K) = Start Date
Dates fall under column K as well K3:K78
7/16/08
8/15/08
8/1/08
6/4/08
7/1/08
7/31/08
7/2/08

Here are my formulas that didn't work:
=IF(AND(">=07/01/08","<=07/31/08"),COUNT(K3:K78),"")
This formula returns a #Value!

=COUNTIF(K3:K78,"<=07/31/08")+COUNTIF(K3:K78,">=07/01/08")
This formula counts the 7/1/08 and 7/31/08 twice
 
M

Mike H

Iona,

The basic idea in counting date ranges is you count everything greater then
and equal to your start date then subtract everything greater than and equal
to your end date. Try this. FWIW I would embed the date in the formula I'd
use a cell reference that contains the date. Note I've changed the date
format to match my machine so change it back to match yours.

=COUNTIF(K3:K78,">=1/9/2008")-COUNTIF(K3:K78,">=1/10/2008")

Mike
 
S

sameoldcalvin

You need to refer to a cell when you compare something, and your
bracket should include the <= mark as well:

=IF(AND(K1>="07/01/08",K1<="07/31/08"),COUNT(K3:K78),"")

I hope this helps.
 
T

T. Valko

One way:

=COUNTIF(K3:K78,">="&DATE(2008,7,1))-COUNTIF(K3:K78,">"&DATE(2008,7,31))

Or, use cells to hold the date boundaries:

A1 = 7/1/2008
B1 = 7/31/2008

=COUNTIF(K3:K78,">="&A1)-COUNTIF(K3:K78,">"&B1)
 
I

Iona

Thank you so much!

PS I love 2007 it has Countifs. I have that installed but build reports in
2003 for my Employer. You were very helpful!
 
I

Iona

LOL! Understood and fair enough. I appreciate your help!

I have another formula I am trying to convert from 2007 to 2003. Do you
think you could help me "again". I tried the same format and it didn't work.
Not sure if it is because it is in a seperate sheet, but same workbook? Here
is the formula from 2007. And I am doing the same thing. Counting a date
range for one column of cells. This should be basic for me but am having
difficulty. Total User Error.

=COUNTIFS('Site_Customer Log'!Q5:Q120,"<=08/31/08",'Site_Customer
Log'!Q5:Q120,">=08/01/08")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top