Change footer date format for Spanish date

[wd6849]

I need to change the format of the date in my footer to display it in Spanish with an alphabetic month, such as 01-ene-2023.

I found a process to modify the format for a date in my footer using a VBA module at

How to specify date or datetime format in header or footer?

www.extendoffice.com

Here's the script:

Sub add_date_footer()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy")
End Sub



This will return the date for 01/01/2023 as 01 Jan 2023

However, I need to display the month abbreviation in Spanish (without changing my computer to Spanish).
In this case it should be 01 ene 2023.

Is there a way to do a VLOOKUP or something similar in the Format() for a page footer ?

Thanks for your help.

 

[kevin9999]

There's probably a more elegant solution than this, but it seems to work:

Option Explicit
Sub add_date_footer()
    Dim i As Long, EspM  As String
    i = Month(Date)
    
    Select Case i
        Case Is = 1: EspM = "ene"
        Case Is = 2: EspM = "Feb"
        Case Is = 3: EspM = "mar"
        Case Is = 4: EspM = "abr"
        Case Is = 5: EspM = "may"
        Case Is = 6: EspM = "jun"
        Case Is = 7: EspM = "jul"
        Case Is = 8: EspM = "ago"
        Case Is = 9: EspM = "sep"
        Case Is = 10: EspM = "oct"
        Case Is = 11: EspM = "nov"
        Case Is = 12: EspM = "dic"
    End Select
    
    ActiveSheet.PageSetup.LeftFooter = Format(Date, "dd ") & EspM & Format(Date, " yyyy")
End Sub

 

[wd6849]

Brilliant !! Thanks so much.

 

[kevin9999]

Brilliant !! Thanks so much.

Happy to help, and thanks for the feedback