Need to turn a number into a date on Excel

Question

I think we need VBA to do this but: I need everything in column A to become a date following the number of the sheet.

i have 12 sheets named jan - fev - mar - abr ..... - dez (names of the month in portuguese) In sheet "jan" i need everything that is typed on column A to become a date of month 01, like :

I type 5 in a15, it becomes 05/01/2024 (day/month/year) If i type 12 in a17 on sheet abr(april) it becomes 12/04/2024 but i can't figure out how!

I already tried using chat gpt vba but it doesnt work.

Answer 1

Enter the following code into the ThisWorkbook code sheet:

Note: Code edited to test that worksheet is a Month sheet.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rg As Range, rgInt As Range
    Dim months, monthNum As Long, lastDay As Long, yr As Long
    
'Change if your abbreviations are different
months = Array("jan", "fev", "mar", "abr", "mai", "jun", "jul", "ago", "set", "out", "nov", "dez")

'check that worksheet is a month sheet
    On Error GoTo exitsub
        monthNum = Application.WorksheetFunction.Match(Sh.Name, months, 0)
    ' On Error GoTo 0
    
    Set rgInt = Intersect(Target, Sh.Columns(1))
    If Not rgInt Is Nothing Then
        Application.EnableEvents = False
            yr = Year(Date)
            lastDay = Day(DateSerial(yr, monthNum + 1, 0))
            
'ensure day number is valid

            For Each rg In rgInt
                If rg.Value >= 1 And rg.Value <= lastDay Then
                    rg.Value = DateSerial(yr, monthNum, rg.Value)
                Else
                    rg.ClearContents
                End If
            Next rg
    End If
    
exitsub:
    Application.EnableEvents = True
End Sub