How do I count the number of records within a date range
 

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003
= 2

How do I count the number of records within a date range
 

=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

Format cell as General


"Mike" wrote:

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003
= 2

How do I count the number of records within a date range
 

try
=sumproduct((year(daterng)=2003)*1)

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and
31/12/2003
= 2

How do I count the number of records within a date range
 

"Teethless mama" wrote in message
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

This may cause problems in locales that use non-USA date styles. Better to
use the DATE function to create the dates.

=COUNTIF(A1:A20,"<"&DATE(2006,12,31))-COUNTIF(A1:A20,"<"&DATE(2006,1,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Teethless mama" wrote in message
...
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

Format cell as General


"Mike" wrote:

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and
31/12/2003
= 2

How do I count the number of records within a date range
 

This worked a treat as I am not using USA style - thank you - Mike

"Chip Pearson" wrote:

"Teethless mama" wrote in message
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

This may cause problems in locales that use non-USA date styles. Better to
use the DATE function to create the dates.

=COUNTIF(A1:A20,"<"&DATE(2006,12,31))-COUNTIF(A1:A20,"<"&DATE(2006,1,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Teethless mama" wrote in message
...
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

Format cell as General


"Mike" wrote:

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and
31/12/2003
= 2

How do I count the number of records within a date range
 

In my case i have a range for a given month on a spreadsheet and I want a
tally of timely received applications indicated on a summary spreadsheet, the
application is timely if the date received is between the 1st and the 15th of
the given month, will this formula subbing out the dates each month work?
Right now I am waiting until the 16th of each month to enter the number of
applications received manually and I want to automate the process.

Thank you for your assistance.
--
Judy Rose Cohen


"Chip Pearson" wrote:

"Teethless mama" wrote in message
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

This may cause problems in locales that use non-USA date styles. Better to
use the DATE function to create the dates.

=COUNTIF(A1:A20,"<"&DATE(2006,12,31))-COUNTIF(A1:A20,"<"&DATE(2006,1,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Teethless mama" wrote in message
...
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

Format cell as General


"Mike" wrote:

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and
31/12/2003
= 2

How do I count the number of records within a date range
 

Hi Judy

That would work fine, substituting your dates in the formula.

You could also enter the date for the month that you require in a cell e.g.
in C1 enter 01 May 2008, and with your dates in column A of Sheet1, then
=SUMPRODUCT((MONTH($A$1:$A$1000)=MONTH($C$1))*(DAY ($A$1:$A$1000)<16)*($A$1:$A$1000)<""))

Change the ranges to suit.
You only need to alter the Date in C1 when you want the figures for another
Month
--
Regards
Roger Govier

"Judy Rose" wrote in message
...
In my case i have a range for a given month on a spreadsheet and I want a
tally of timely received applications indicated on a summary spreadsheet,
the
application is timely if the date received is between the 1st and the 15th
of
the given month, will this formula subbing out the dates each month work?
Right now I am waiting until the 16th of each month to enter the number of
applications received manually and I want to automate the process.

Thank you for your assistance.
--
Judy Rose Cohen


"Chip Pearson" wrote:

"Teethless mama" wrote in
message
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

This may cause problems in locales that use non-USA date styles. Better
to
use the DATE function to create the dates.

=COUNTIF(A1:A20,"<"&DATE(2006,12,31))-COUNTIF(A1:A20,"<"&DATE(2006,1,1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Teethless mama" wrote in
message
...
=COUNTIF(A1:A100,"="&"1/1/2003")-COUNTIF(A1:A100,""&"12/31/2003")

Format cell as General


"Mike" wrote:

How do I count the number of records within a date range.
eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and
31/12/2003
= 2