 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Count dates -in text format- in October
Hi everyone, I have column filled with dates that, unfortunately, are text and therefore cannot be viewed as numbers. The problem I have to solve is to count all dates in the column that represent a day in, say, October (or any month). My column looks like this: A 12-10-2009 14-11-2009 17-10-2009 17-10-2009 15-10-2009 12-11-2009 (etcetera) It would be nice if I could use something like =COUNTIF(A1:A6;MID(XXX;3;4)="-10-") but of course this won't work; there's nothing concrete to be substituted for XXX. Can this be done with a worksheet function and if so, what would this function look like? Thank you, Cooz PS I use Excel 2000 PS I know how to do this with VBA but since the A1:A6 range I mentioned above is actually Much Longer, using a worksheet function would produce much faster a result than counting and checking individual cells with VBA. =SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10))  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi everyone, > [quoted text clipped - 28 lines] > much > faster a result than counting and checking individual cells with VBA. Hi Bob, Yep, this is what I wanted. Works great. Thank you, Cooz > =SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10)) > [quoted text clipped - 30 lines] > > much > > faster a result than counting and checking individual cells with VBA. Hello Bob, If I may take some more of your time... When I look in the XL help on SUMPRODUCT, I can't find anything on using this function with criteria. Yet your solution works perfectly. The help doesn't mention "--" either. Adding both: Does the "--" operator denote the usage of criteria? Can I use this operator with other functions as well? Thank you once again, Cooz > =SUMPRODUCT(--(MONTH(DATEVALUE(A1:A6))=10)) > [quoted text clipped - 30 lines] > > much > > faster a result than counting and checking individual cells with VBA. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello Bob, > [quoted text clipped - 50 lines] >> > much >> > faster a result than counting and checking individual cells with VBA. How about: =COUNTIF(C1:C6,"*-10-*") Regards, Fred. > Hi everyone, > [quoted text clipped - 28 lines] > much > faster a result than counting and checking individual cells with VBA. Hi Fred, Ok! You can use wildcards with countif! I learn a lot here. Thank you, Cooz > How about: > [quoted text clipped - 35 lines] > > much > > faster a result than counting and checking individual cells with VBA. Hi Try =COUNTIF(A:A,"=*10*") SignatureRegards Roger Govier > Hi everyone, > [quoted text clipped - 28 lines] > much > faster a result than counting and checking individual cells with VBA. Good idea. Works fine. Thank you, Cooz > Hi > [quoted text clipped - 33 lines] > > much > > faster a result than counting and checking individual cells with VBA. I would suggest using the dashes around the 10 like Fred showed, otherwise the formula fails for the 10 of any month. Rick > Hi > [quoted text clipped - 33 lines] >> much >> faster a result than counting and checking individual cells with VBA. Very true, Rick. Mea Culpa. SignatureRegards Roger Govier > I would suggest using the dashes around the 10 like Fred showed, otherwise > the formula fails for the 10 of any month. [quoted text clipped - 40 lines] >>> much >>> faster a result than counting and checking individual cells with VBA. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.