Excel Hints

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
    24,101
    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
    24,101
    Location
    For completeness

    [vba]

    Dim sMonthName As String

    sMonthName = "October"
    MsgBox Month(DateValue("01-" & sMonthName & "-1900"))
    [/vba]

  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):

    [vba]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[/vba]




    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
  •