View Full Version : Solved: Convert a month name to a month number
malik641
10-25-2006, 02:46 PM
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
Bob Phillips
10-25-2006, 02:55 PM
With the month name in A1
=MONTH(1&A1)
Charlize
10-25-2006, 02:57 PM
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
Bob Phillips
10-25-2006, 02:57 PM
For completeness
Dim sMonthName As String
sMonthName = "October"
MsgBox Month(DateValue("01-" & sMonthName & "-1900"))
malik641
10-25-2006, 03:03 PM
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):
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.