Microsoft Excel Webinar

Results 1 to 5 of 5

Thread: Solved: Convert a month name to a month number

  1. #1
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location

    Solved: Convert a month name to a month number

    Hey guys

    How can I take "October" and convert it to the corresponding month number 10?

    I thought Format() would be the way to go, but I'm having no luck...

    I'm not looking for a Select Case statement (or big nested IF), but a VBA (or worksheetfunction) native way.

    This seems easy, but I can't figure it out...

    TIA




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,942
    Location
    With the month name in A1

    =MONTH(1&A1)

  3. #3
    is october in dateformat or just the name. I mean, is it 25/10/2006 (25th of october) or the text october ? Maybe with autocorrect ??? in a certain column ???

    Charlize

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    23,942
    Location
    For completeness

    VB:
     
    Dim sMonthName As String 
     
    sMonthName = "October" 
    MsgBox Month(DateValue("01-" & sMonthName & "-1900")) 
    
    
    Formatting tags added by mark007

  5. #5
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Awesome Thanks Bob


    To clear things up a little more, I wanted a VBA way to do it...it has nothing to do with the cells. I have a workbook that has the month's names as worksheets (so the months are Strings, Charlize ) and keeps records of monthly data. For fun I decided to make (well, start to make) a procedure that adds a worksheet corresponding to the next month using a template.

    I just started, but this is what the code is so far (just testing Bob's code):

    VB:
    Sub NewMonth() 
        Dim ws As Excel.Worksheet 
        Dim HighestMonth As Long 
        For Each ws In ThisWorkbook.Worksheets 
            If ws.Visible = xlSheetVisible Then 
                If Month(DateValue("01-" & ws.Name & "-1900")) > HighestMonth Then 
                    HighestMonth = Month(DateValue("01-" & ws.Name & "-1900")) 
                End If 
            End If 
        Next ws 
        Debug.Print HighestMonth 
    End Sub 
    
    
    Formatting tags added by mark007




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •