Count Dates Help?
 

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

Count Dates Help?
 

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?

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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

Count Dates Help?
 

I used a 'helper row'.
In H11 I used =IF(AND(ISNUMBER(H10),H1038913),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 V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ken" wrote in message
...
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

Count Dates Help?
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ken" wrote in message
...
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

Count Dates Help?
 

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

Thanks ... Kha

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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

Count Dates Help?
 

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ken" wrote in message
...
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

Count Dates Help?
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
T. ... YES ... As June rolls around the Formula must now check for JUNE
Dates.

Thanks ... Kha

"T. Valko" wrote:

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?

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
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

Count Dates Help?
 

How about
=SUMPRODUCT(--(TEXT(H10:DC10,"m")=MONTH(C4)))
or
=SUMPRODUCT(--(TEXT(H10:DC10,"myyyy")=text(C4,"myyyy")))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ken" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ken" wrote in message
...
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