Count Dates Help?

[Ken]

Excel2003 ... Range H10C10 contains a Formula which returns ... TEXT, DATEs
& SPACEs ("") ... Range Format = Dates 3/14

I want formula in Cell E135 to return number of DATEs occurring THIS Month
only (exclude: TEXT, DATES (not this month), & SPACEs).

Thanks ... Kha

 

[T. Valko]

DATEs occurring THIS Month

Assuming THIS month means May 2009...

=SUMPRODUCT(--(TEXT(H10C10,"myyyy")="52009"))

Does THIS month mean that as of June 1 the formula should only count dates
for June 2009?

 

[Bernard Liengme]

I used a 'helper row'.
In H11 I used =IF(AND(ISNUMBER(H10),H10>38913),MONTH(H10),NA())
I copied this across to DC10
The I use =COUNTIF(H11:L11,MONTH(TODAY())) to get the required count

Other approaches gave me VALUE errors. Note that 38913 is the last day of
2009 (31 Dec 2008)
best wishes

 

[Bernard Liengme]

Following from Biff's great idea:
=SUMPRODUCT(--(TEXT(H10C10,"myyyy")=MONTH(TODAY())&"2009"))
This is give you the count for the CURRENT month
best wishes

 

[Ken]

T. ... YES ... As June rolls around the Formula must now check for JUNE Dates.

Thanks ... Kha

 

[Ken]

Bernard ... (Hi)

This formula works ... However, like "Ts" solution when I roll into 2010 I
am going to have a problem ... If it helps ... I could key off of Cell D4
which contains a date occurring in May (or whatever current month is ... I
enter this date each month).

Thanks ... Kha

 

[T. Valko]

Try this:

=SUMPRODUCT(--(TEXT(H10C10,"myyyy")=TEXT(NOW(),"myyyy")))

 

[Bernard Liengme]

How about
=SUMPRODUCT(--(TEXT(H10C10,"m")=MONTH(C4)))
or
=SUMPRODUCT(--(TEXT(H10C10,"myyyy")=text(C4,"myyyy")))
best wishes