Date Function Help

  • Thread starter Thread starter shelfish
  • Start date Start date
S

shelfish

I need some assistance writing a formula that will count how many
dates (in a list of dates in m/d/y format) are in each month. So...

Column A [1/1/08, 2/7/08, 10/19/08, ...]
Column B [Months, Jan, Feb, Mar, ...]
Column C [Counts, {count of dates in January}, {count of dates in
February}, ...]

Make sense?

The obvious answer would be to use another column to extract the month
values and then just perform a COUNTIF function. But I can't use an
extra column.

Any help is appricated.

Thanks,
Shelton
 
Try this in C1:

=SUMPRODUCT(--(MONTH(A$1:A$100)=ROW(A1)),--((A$1:A$100)<>""))

and copy down. This ignores years, so dates from January 2007 and from
January 2008 will be counted in with the January totals.

Hope this helps.

Pete
 
I need some assistance writing a formula that will count how many
dates (in a list of dates in m/d/y format) are in each month. So...

Column A [1/1/08, 2/7/08, 10/19/08, ...]
Column B [Months, Jan, Feb, Mar, ...]
Column C [Counts, {count of dates in January}, {count of dates in
February}, ...]

Make sense?

The obvious answer would be to use another column to extract the month
values and then just perform a COUNTIF function. But I can't use an
extra column.

Any help is appricated.

Thanks,
Shelton
Click to expand...

Something like:

=SUMPRODUCT(--(MONTH(Dts)*(LEN(Dts)>0)=ROWS($1:1)))

entered in the column next to where you have Jan, and fill down to Dec, will
give you a count for each month.

--ron
 
Wow! I consider myself pretty advanced but you got me.

I'm getting a #VALUE error.

I see the two arrays in the formula, but I don't have any blanks so I
should only need the first one right?

I replaced the range you had with my actual range...no luck
I replaced the range you had with the name of the
range("claim_date")...no luck
I tried it as an {array function}...no luck...and not...no luck

Here's exactly what I have now:
=SUMPRODUCT(--(MONTH(claim_date)=ROW(A1)),--((claim_date)<>""))

Question: What do the double-dashes mean?

Thanks again!

S.
 
Ron,

That works well. Still, what function do the double dashes perform?

Many thanks for the help.
S.
 
Ron,

That works well. Still, what function do the double dashes perform?

Many thanks for the help.
S.
Click to expand...

The double dashes convert the Boolean value into a number. In Excel TRUE
converts to 1 and FALSE to zero.
--ron
 
Back
Top