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...:dunno

TIA

With the month name in A1

=MONTH(1&A1)

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

For completeness

[vba]

Dim sMonthName As String

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

Awesome :thumb Thanks Bob :clap:


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]