| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Dates between Dates exclude Text | Excel Discussion (Misc queries) | |||
| I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
| Count Dates? | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
| count dates | New Users to Excel | |||
Linear Mode
