Count Dates Help?

K

Ken

Excel2003 ... Range H10:DC10 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

T. Valko

DATEs occurring THIS Month

Assuming THIS month means May 2009...

=SUMPRODUCT(--(TEXT(H10:DC10,"myyyy")="52009"))

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

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
 
B

Bernard Liengme

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

Ken

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

Thanks ... Kha
 
K

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
 
B

Bernard Liengme

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top