RE: $$Excel-Macros$$ how to get month Occurrence in no.
Hi, XL is always looking for the opportunity to convert something to a date. In my region, where we use day, month, year, and with A1 containing the word 'July': =--(1&A1) XL reads this as the 1st day of July, then adds the current year as a default. It returns 40360, which if the cell is formatted as date, shows 1 July 2010 Interestingly, =--(A1&1) also works. XL reads this as July 2001, and provides a default day of 1, returning the same date as above. In either event, now that XL has been provided an actual date, we can apply the MONTH thingy, which then returns 7 Regards - Dave. Date: Wed, 29 Dec 2010 12:12:16 +0500 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: sajidmansooral...@gmail.com To: excel-macros@googlegroups.com Really Impressive! Let me know how this formula works On Tue, Dec 28, 2010 at 3:43 PM, Dave Bonallack wrote: Hi, A shorter version would be: =MONTH(--(1&A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word "April" or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks & regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ how to get month Occurrence in no.
Really Impressive! Let me know how this formula works On Tue, Dec 28, 2010 at 3:43 PM, Dave Bonallack wrote: > Hi, > A shorter version would be: > =MONTH(--(1&A1)) > This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended > abbreviations (eg Sept) and also unabbreviated (eg June, November) > Regards - Dave. > > -- > Date: Tue, 28 Dec 2010 15:34:01 +0700 > Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. > From: villager.g...@gmail.com > To: excel-macros@googlegroups.com > > > If the word "April" or *another month name *is type correctly in cell A1 > try this formula in B1 > > =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") > > > > On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: > > Hi experts, > > is there any formula, if i only type in cell April and the other cell > return the value 4, remember if i type simply April not 04/01/2010 etc. etc. > > please provide your feedback > > thanks & regards > > ROHAN > 9818247278, 8860567680 > > > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
RE: $$Excel-Macros$$ how to get month Occurrence in no.
Thanks! Not my own though. Learnt it on another group. Happy to pass it on. Regards - Dave Date: Tue, 28 Dec 2010 17:44:51 +0530 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: dilipan...@gmail.com To: excel-macros@googlegroups.com Impressive ..!! Good show Dave, Siti, STDEV :) Best Regards, DILIPandey On Tue, Dec 28, 2010 at 4:13 PM, Dave Bonallack wrote: Hi, A shorter version would be: =MONTH(--(1&A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word "April" or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks & regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- Thanks & Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ how to get month Occurrence in no.
Impressive ..!! Good show Dave, Siti, STDEV :) Best Regards, DILIPandey On Tue, Dec 28, 2010 at 4:13 PM, Dave Bonallack wrote: > Hi, > A shorter version would be: > =MONTH(--(1&A1)) > This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended > abbreviations (eg Sept) and also unabbreviated (eg June, November) > Regards - Dave. > > -- > Date: Tue, 28 Dec 2010 15:34:01 +0700 > Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. > From: villager.g...@gmail.com > To: excel-macros@googlegroups.com > > > If the word "April" or *another month name *is type correctly in cell A1 > try this formula in B1 > > =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") > > > > On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: > > Hi experts, > > is there any formula, if i only type in cell April and the other cell > return the value 4, remember if i type simply April not 04/01/2010 etc. etc. > > please provide your feedback > > thanks & regards > > ROHAN > 9818247278, 8860567680 > > > > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- Thanks & Regards, DILIP KUMAR PANDEY MBA-HR,B.Com(Hons),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 62, India -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
RE: $$Excel-Macros$$ how to get month Occurrence in no.
Hi, A shorter version would be: =MONTH(--(1&A1)) This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended abbreviations (eg Sept) and also unabbreviated (eg June, November) Regards - Dave. Date: Tue, 28 Dec 2010 15:34:01 +0700 Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no. From: villager.g...@gmail.com To: excel-macros@googlegroups.com If the word "April" or another month name is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: Hi experts, is there any formula, if i only type in cell April and the other cell return the value 4, remember if i type simply April not 04/01/2010 etc. etc. please provide your feedback thanks & regards ROHAN 9818247278, 8860567680 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ how to get month Occurrence in no.
other stuff =MONTH(DATEVALUE("1 "&A1&" 2010")) On Tue, Dec 28, 2010 at 3:34 PM, siti Vi wrote: > If the word "April" or *another month name *is type correctly in cell A1 > try this formula in B1 > > =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") > > > > > On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: > >> Hi experts, >> >> is there any formula, if i only type in cell April and the other cell >> return the value 4, remember if i type simply April not 04/01/2010 etc. etc. >> >> please provide your feedback >> >> thanks & regards >> >> ROHAN >> 9818247278, 8860567680 >> >> >> > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ how to get month Occurrence in no.
If the word "April" or *another month name *is type correctly in cell A1 try this formula in B1 =TEXT(DATEVALUE("1 "&A1&" 2010"),"M") On Tue, Dec 28, 2010 at 1:56 PM, Rohan Young wrote: > Hi experts, > > is there any formula, if i only type in cell April and the other cell > return the value 4, remember if i type simply April not 04/01/2010 etc. etc. > > please provide your feedback > > thanks & regards > > ROHAN > 9818247278, 8860567680 > > > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
Re: $$Excel-Macros$$ how to get month Occurrence in no.
try this.. Function convertMonthName2Number(monthName As String) As Integer ' try to convert month name to actual date type Dim dtestr As String dtestr = monthName & "/1/2000" Dim dte As Date On Error Resume Next dte = CDate(dtestr) If Err.Number <> 0 Then convertMonthName2Number = -999 Exit Function End If On Error GoTo 0 convertMonthName2Number = Month(dte) End Function OR you can refer this :- http://www.codeforexcelandoutlook.com/blog/2009/04/converting-month-name-to-number/ Regards, Manoj On Tue, Dec 28, 2010 at 12:26 PM, Rohan Young wrote: > Hi experts, > > is there any formula, if i only type in cell April and the other cell > return the value 4, remember if i type simply April not 04/01/2010 etc. etc. > > please provide your feedback > > > thanks & regards > > ROHAN > 9818247278, 8860567680 > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts