EASY ONE/count occurrences in a date range/excel 03

[jbishard]

I have a range B2:B780 that contain dates formatted as mm/dd/yy. I would like
to count the number of occurrences between 05/01/08 thru 05/31/08. Please
help as I am about to throw my computer out the window.....

 

[Laroche J]

jbishard wrote on 2009-05-16 14:18:

I have a range B2:B780 that contain dates formatted as mm/dd/yy. I would like
to count the number of occurrences between 05/01/08 thru 05/31/08. Please
help as I am about to throw my computer out the window.....

Since you have two criteria you cannot use a single COUNTIF. You could use
DCOUNT or DCOUNTA, but try this:

You can enter the two control dates directly in the formula, but I suggest
putting them in their own respective cell, so you can check any date range
without touching the formula. Let's say here they are in D1 and D2.

So your formula would be:
=COUNTIF(B2:B780,">="&D1)-COUNTIF(B2:B780,">"&D2)


JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial

 

[jbishard]

JL: That doesn't seem to work. It doesn't count the # of occurrences. It
returns a date of 03/23/00. I have 05/01/08 as my D1 & 05/31/08 as my D2.
Please help...thanks

 

[Laroche J]

Make sure you did a copy and paste of my formula, to avoid any typing error.
03/23/00 is actually the value 35146, much too high a count for your source
range.

Then format the cell with the formula as General or Number. Excel tried to
be smart seeing that your formula was dealing with dates, which in this case
was misguided assistance.

JL
Mac OS X 10.4.11
Office v.X 10.1.9


jbishard wrote on 2009-05-18 12:27:

 

[jbishard]

It's a miracle!! Thanks so much. I formatted the cell that contained the
formula as "general" & that did it! I've never taken a course on Excel but I
do enjoy learning new ways to use it. I must admit I have a love/hate
relationship with it. Thanks again.

 

[Laroche J]

jbishard wrote on 2009-05-18 19:24:

It's a miracle!! Thanks so much. I formatted the cell that contained the
formula as "general" & that did it! I've never taken a course on Excel but I
do enjoy learning new ways to use it. I must admit I have a love/hate
relationship with it. Thanks again.

You're welcome. I had not considered the fact that 03/23/00 was in fact in
year 1900 rather than 2000, which actually translates to 83 as a number (ah!
the pitfalls of the two-digit year). Your formula was probably typed right.

I bet your file originates from Windows since it's using the 1900 date
system. Mac Excel usually defaults to the 1904 date system, which means that
day 0 is January 1st, 1904. In the 1900 date system day 1 is January 1st,
1900 (day 0 would be December 31st, 1899, but Excel doesn't accept that
value. It does however convert it to January 0th, 1900 !!!)

JL

 

[Phillip Jones, C.E.T.]

jbishard wrote on 2009-05-18 19:24:


You're welcome. I had not considered the fact that 03/23/00 was in fact in
year 1900 rather than 2000, which actually translates to 83 as a number (ah!
the pitfalls of the two-digit year). Your formula was probably typed right.

I bet your file originates from Windows since it's using the 1900 date
system. Mac Excel usually defaults to the 1904 date system, which means that
day 0 is January 1st, 1904. In the 1900 date system day 1 is January 1st,
1900 (day 0 would be December 31st, 1899, but Excel doesn't accept that
value. It does however convert it to January 0th, 1900 !!!)

JL

To enter a date properly for date prior to 2000 it 12/12/12

for dates 2000 and above is 12/12/2012