Macro code changing date format

Beneindias

Board Regular
Joined
Jun 21, 2022
Messages
120
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi to all of you,

I have an excel sheet with an Inputbox, where i'm asker to put the number of the month, the year and a third value.

Problem is, it was working as intended during 2023, but now, it's swaping the month for the day.

I'm in Europe, we use dd/mm/yyyy and I'm trying to make the file for February 2024 (month 2).

But, as I do this, the date in cell D1 is always transformed as "02/01/2024" when I want "01/02/2024".
If I go to cell D1 and manualy enter "01/02/2024" it stays how it should.

I don't think that the problem is in the macro code, but I can't find a solution to this.
Cell is formated as Date dd/mm/yyyy.

My code is this one:

VBA Code:
Public Sub MyInputBox()

    Dim MyInputMonth As String
    Dim MyInputYear As String
    Dim MyInputPago As String
    Dim NumericInputMonth As Boolean
    Dim NumericInputYear As Boolean
    Dim NumericInputPago As Boolean
    Dim MyInputDate As String

    
    'Box to user input
    'Month number
    MyInputMonth = InputBox("Escrever número do novo mês", "Iniciar novo mês", "Número do novo mês")
    NumericInputMonth = IsNumeric(MyInputMonth)
    
    'Year number
    MyInputYear = InputBox("Escrever número do ano", "Iniciar novo mês", "Número do ano")
    NumericInputYear = IsNumeric(MyInputYear)
    
    'Value
    MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
    NumericInputPago = IsNumeric(MyInputPago)

    
    'Checks if month number is between 1 and 12
    If NumericInputMonth Then
        If MyInputMonth >= 1 And MyInputMonth <= 12 Then
        
            MyInputDate = 1 & "-" & MyInputMonth & "-" & MyInputYear
    
            'Calls macro to delete table cells
            Call Clearcells
    
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            
            Range("G53").Value = MyInputPago
            

                    
        Else
            'GoTo errHand
            MsgBox "Número de mês mal introduzido"
        End If
    Else
        'GoTo errHand
        MsgBox "Tem que introduzir número do mês"
    End If
    
    Exit Sub
'errHand:
    'MsgBox "Número de mês mal introduzido"
    
End Sub


Thank you all in advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
We'll try the easy way first, try making the changes in red and see if you get the correct results

Rich (BB code):
            MyInputDate = MyInputYear & "-" & MyInputMonth & "-" & 1
    
            'Calls macro to delete table cells
            Call Clearcells
    
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            Range("D1").NumberFormat = "dd/mm/yyyy"
            
            Range("G53").Value = MyInputPago
 
Upvote 0
Solution
Hi
you are posting a string date to your range so excel likely to default to USA date format.
To overcome this you can use one of the available Type Conversion Functions to coerce your string date to date.

See if this update to your code helps you

VBA Code:
Public Sub MyInputBox()
    
    Dim MyInputMonth As Variant
    Dim MyInputYear As Variant
    Dim MyInputPago As Variant
    
    Dim MyInputDate As Date
    
    'Box to user input
    'Month number
    Do
        MyInputMonth = InputBox("Escrever número Do novo mês", "Iniciar novo mês", "Número Do novo mês")
        'cancel pressed
        If StrPtr(MyInputMonth) = 0 Then Exit Sub
    Loop Until Val(MyInputMonth) >= 1 And Val(MyInputMonth) <= 12
    
    'Year number
    Do
        MyInputYear = InputBox("Escrever número Do ano", "Iniciar novo mês", "Número Do ano")
        'cancel pressed
        If StrPtr(MyInputYear) = 0 Then Exit Sub
    Loop Until MyInputYear Like "####"
    
    'Value
    Do
        MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
        'cancel pressed
        If StrPtr(MyInputPago) = 0 Then Exit Sub
    Loop Until IsNumeric(MyInputPago)
    
    MyInputDate = CDate(1 & "/" & MyInputMonth & "/" & MyInputYear)
    
    'Calls macro to delete table cells
    Call ClearCells
    
    'Adds date to cell D1
    With Range("D1")
        .Value = MyInputDate
        .NumberFormat = "dd/mm/yyyy"
    End With
    
    Range("G53").Value = Val(MyInputPago)
    
End Sub

I have used CDate function which uses your regional settings & should hopefully, produce correct result but there are other alternatives you can try if still have the same issue.

Dave
 
Upvote 0
We'll try the easy way first, try making the changes in red and see if you get the correct results

Rich (BB code):
            MyInputDate = MyInputYear & "-" & MyInputMonth & "-" & 1
   
            'Calls macro to delete table cells
            Call Clearcells
   
            'Adds date to cell D1
            Range("D1").Value = MyInputDate
            Range("D1").NumberFormat = "dd/mm/yyyy"
           
            Range("G53").Value = MyInputPago
This solution worked.

But I still find it strange, because my code was working fine in 2023.

Thanks for your help
 
Upvote 0
Hi
you are posting a string date to your range so excel likely to default to USA date format.
To overcome this you can use one of the available Type Conversion Functions to coerce your string date to date.

See if this update to your code helps you

VBA Code:
Public Sub MyInputBox()
   
    Dim MyInputMonth As Variant
    Dim MyInputYear As Variant
    Dim MyInputPago As Variant
   
    Dim MyInputDate As Date
   
    'Box to user input
    'Month number
    Do
        MyInputMonth = InputBox("Escrever número Do novo mês", "Iniciar novo mês", "Número Do novo mês")
        'cancel pressed
        If StrPtr(MyInputMonth) = 0 Then Exit Sub
    Loop Until Val(MyInputMonth) >= 1 And Val(MyInputMonth) <= 12
   
    'Year number
    Do
        MyInputYear = InputBox("Escrever número Do ano", "Iniciar novo mês", "Número Do ano")
        'cancel pressed
        If StrPtr(MyInputYear) = 0 Then Exit Sub
    Loop Until MyInputYear Like "####"
   
    'Value
    Do
        MyInputPago = InputBox("Adicionar valor pago", "Valor pago", "Valor pago!")
        'cancel pressed
        If StrPtr(MyInputPago) = 0 Then Exit Sub
    Loop Until IsNumeric(MyInputPago)
   
    MyInputDate = CDate(1 & "/" & MyInputMonth & "/" & MyInputYear)
   
    'Calls macro to delete table cells
    Call ClearCells
   
    'Adds date to cell D1
    With Range("D1")
        .Value = MyInputDate
        .NumberFormat = "dd/mm/yyyy"
    End With
   
    Range("G53").Value = Val(MyInputPago)
   
End Sub

I have used CDate function which uses your regional settings & should hopefully, produce correct result but there are other alternatives you can try if still have the same issue.

Dave
Thanks for your help.

Didn't tried your code, because the other solution was easier to implement in various files and it worked.

Thank for your help
 
Upvote 0
This solution worked.

But I still find it strange, because my code was working fine in 2023.

Thanks for your help
You're welcome, your code would have worked for certain if the day was higher than the 12th of the month... maybe that was how it was setup in 2023.

If you were getting the date from a cell or if it was a string in VBA like "01/02/2024" you would have needed to do a type conversion like dmt32 posted but because you were getting the numbers individually I just changed the order so it was in the International date format (yyyy-mm-dd), which VBA doesn't appear to convert to US format (mm/dd/yyyy) when it puts it into the cell.
 
Upvote 0
You're welcome, your code would have worked for certain if the day was higher than the 12th of the month... maybe that was how it was setup in 2023.

If you were getting the date from a cell or if it was a string in VBA like "01/02/2024" you would have needed to do a type conversion like dmt32 posted but because you were getting the numbers individually I just changed the order so it was in the International date format (yyyy-mm-dd), which VBA doesn't appear to convert to US format (mm/dd/yyyy) when it puts it into the cell.
Learning something new every day.
Thanks for the explanation, because, honestly, I wasn't understanding why it was working with your solution.

Thanks for thar
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top