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

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

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

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

"Iona" wrote:

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

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

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.

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

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)

--
Biff
Microsoft Excel MVP


"Iona" wrote in message
...
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

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

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!

"Iona" wrote:

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

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

Your welcome and I don't love 2007 and will stubbornly stick to 2003

"Iona" wrote:

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!

"Iona" wrote:

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

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

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

"Iona" wrote:

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