Date Function Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shelfish - 20 Mar 2008 21:05 GMT

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

Reply to this Message

Pete_UK - 20 Mar 2008 21:24 GMT

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...
[quoted text clipped - 14 lines]
> Thanks,
> Shelton

Reply to this Message

shelfish - 20 Mar 2008 21:48 GMT

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.

Reply to this Message

Pete_UK - 21 Mar 2008 00:11 GMT

It worked fine for me in my tests. Are you sure you have proper dates
in column A?

Pete

> Wow! I consider myself pretty advanced but you got me.
>
[quoted text clipped - 16 lines]
>
> S.

Reply to this Message

Ron Rosenfeld - 20 Mar 2008 21:28 GMT

>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...
[quoted text clipped - 14 lines]
>Thanks,
>Shelton

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

Reply to this Message

shelfish - 20 Mar 2008 21:57 GMT

Ron,

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

Many thanks for the help.
S.

Reply to this Message

Ron Rosenfeld - 20 Mar 2008 22:31 GMT

>Ron,
>
>That works well. Still, what function do the double dashes perform?
>
>Many thanks for the help.
>S.

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

Reply to this Message

Rate this thread: