CODEWITHSUNDEEP
vbaexcel
user9351236
user9351236

Reputation: 143

convert month name (french) to month number

I have an excel in which a sheet is created for every month with the name as kpi_monthname where month name has first 3 characters of the month. Like for month of may, it is being displayed as kpi_mai or for april it is kpi_avr. The month name is in French. I want to take this month name and convert it to month number. I have the code for this conversion if the month name is in English, but for French, it is showing the error.

Please let me know if I can convert the month name to English, when it is being created, or convert the month name (displayed in French) to month number. Either of the solution will work for me.

Please find below code when a new sheet is being created for the current month:

 premier_jour = DateSerial(Year(Date), Month(Date), 1)
 dernier_jour = DateSerial(Year(Date), Month(Date) + 1, 1)
 deb_period_curr = Format(premier_jour, "mmm")

 With ThisWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "analysis_kpi " & deb_period_curr
 End With

For converting month name to month number, please find below code: 

 month_nam = Right(worksheet1.Name, 4)
 month_num = Evaluate("=MONTH(1&" & Chr(34) & month_nam & Chr(34) & ")")
 month_num = WorksheetFunction.Text(month_num, "00")

Upvotes: 1

Views: 2856

Answers (5)

BrakNicku
BrakNicku

Reputation: 5991

Please let me know if I can convert the month name to English, when it is being created

This is pretty easy, to change month name to English (or other languages) replace:

deb_period_curr = Format(premier_jour, "mmm")

With (first 3 letters):

deb_period_curr = WorksheetFunction.Text(premier_jour, "[$-409]mmm")

Or (full name):

deb_period_curr = WorksheetFunction.Text(premier_jour, "[$-409]mmmm")

TEXT formula supports country codes. 409 is US English, but you can find a list of languages and some more info here.

To get 2 digit month number you can use for example:

deb_period_curr = Format(premier_jour, "mm")

Upvotes: 4

Pspl
Pspl

Reputation: 1474

I usually have the same problem (with the Portuguese version of MS Excel). Normally I convert the names (in Portuguese) to the month number (or the other way around) with a simple Select Case:

Dim MonthStr as String
Dim MonthNum as Integer

MonthStr = Cells(1, 1).Text 'Location of the month name

Select Case MonthStr
    Case "Janeiro": MonthNum = 1
    Case "Fevereiro": MonthNum = 2        
    Case "Março": MonthNum = 3
    Case "Abril": MonthNum = 4
    Case "Maio": MonthNum = 5
    Case "Junho": MonthNum = 6
    Case "Julho": MonthNum = 7
    Case "Agosto": MonthNum = 8
    Case "Setembro": MonthNum = 9
    Case "Outubro": MonthNum = 10
    Case "Novembro": MonthNum = 11
    Case "Dezembro": MonthNum = 12
End Select

Honesty I think this is the simplest way to deal with this...

Upvotes: 0

Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

You can also use an array to store month names, this is what I do with english month names:

Dim monthNames as variant

monthNames = Array(,"January","February","March","April","May","June","July","August","September","October","November","December")

To get 5th month's name:

Debug.Print monthNames(5)

To get number of month for string "May":

Dim nCounter as Long
Dim myMonthString as String

myMonthString = "May"

For nCounter = LBound(monthNames) to UBound(monthNames)
    If monthNames(nCounter) = myMonthString then
        Debug.Print nCounter 'month number
        Exit For
    End if
Next nCounter

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

Or a slight variation using an array and match

application.match("fev",Array("Jan","Fev","Mar","Avr","Mai"),0)

Upvotes: 1

avb
avb

Reputation: 1753

use Dictionary object to define french months:

sub m()
Dim months As New Scripting.Dictionary
    months.Add Key:="kpi_mai", Item:=5
    months.Add Key:="kpi_avr", Item:=4
    Debug.Print months("kpi_avr")
End Sub

I added only two months you mentioned cause I don' know the names...
You will have to add reference to Microsoft Scripting Runtime to your project

Upvotes: 1

Related Questions