VBA wrong date format when setting cell value

[martipe1]

I ask through VBA for starting and end dates and set the value in 2 different cells, my problem is that one value is saved as mm/dd/yy and the other one as dd/mm/yy.

My code is:

Sub DateReport()

Dim StartDate, EndDate As Date

StartDate = InputBox("Report Starting Date (dd/mm/aa)", "Report", Format(Now(), "dd/mm/yy"))
StartDate = Format(CDate(StartDate), "dd/mm/yyyy")
Range("StartReport").Value = StartDate

EndDate = InputBox("Report End Date (dd/mm/aa)", "Report", Format(StartDate, "dd/mm/yy"))
EndDate = Format(CDate(EndDate), "dd/mm/yyyy")
Range("EndReport").Value = EndDate

End Sub

 

[rlv01]

I'm not sure I understand your problem, but maybe this:

Sub DateReport()
    Dim StartDate, EndDate As Date
    
    StartDate = InputBox("Report Starting Date (dd/mm/aa)", "Report", Format(Now(), "dd/mm/yy"))
    StartDate = Format(CDate(StartDate), "dd/mm/yyyy")
    With Range("StartReport")
        .NumberFormat = "mm/dd/yy"
        .Value = StartDate
    End With
    
    EndDate = InputBox("Report End Date (dd/mm/aa)", "Report", Format(StartDate, "dd/mm/yy"))
    EndDate = Format(CDate(EndDate), "dd/mm/yyyy")
    With Range("EndReport")
        .NumberFormat = "mm/dd/yy"
        .Value = EndDate
    End With
End Sub

 

[martipe1]

Thank you for your comments.

With my code the start date format was mm/dd/yy and end date dd/mm/yy

After I included the ñines that you commented, the result is the opposite, start date format is dd/mm/yy and end date mm/dd/yy

Have no idea why the format changes when setting cell value

 

[martipe1]

Sorry, but was wrong still same result

First date format is mm/dd/yy and second dd/mm/yy

I changed your code to format mmmm-yyyy

The default is today's meaning I get for first date July 2021 and for second September 2021

 

[Akuini]

How about:
Note: I changed the range

Sub DateReport2()
    Dim StartDate As Date, EndDate As Date
 
    StartDate = CDate(InputBox("Report Starting Date (dd/mm/aa)", "Report", Format(Now(), "dd/mm/yy")))
    With Range("A1")
        .NumberFormat = "mm/dd/yy"
        .Value = StartDate
    End With
 
    EndDate = CDate(InputBox("Report End Date (dd/mm/aa)", "Report", Format(StartDate, "dd/mm/yy")))
    With Range("b1")
        .NumberFormat = "mm/dd/yy"
        .Value = EndDate
    End With
End Sub

Edit:
Actually your Dim statement is wrong, it should be:
Dim StartDate As Date, EndDate As Date
So amend your Dim statement in your original code (post #1), see what happens.

 

[Zot]

Dim StartDate, EndDate As Date would mean

StartDate as Variant
EndDate as Date

that is why @Akuini said the statement is wrong

 

[martipe1]

How about:
Note: I changed the range

Sub DateReport2()
    Dim StartDate As Date, EndDate As Date
 
    StartDate = CDate(InputBox("Report Starting Date (dd/mm/aa)", "Report", Format(Now(), "dd/mm/yy")))
    With Range("A1")
        .NumberFormat = "mm/dd/yy"
        .Value = StartDate
    End With
 
    EndDate = CDate(InputBox("Report End Date (dd/mm/aa)", "Report", Format(StartDate, "dd/mm/yy")))
    With Range("b1")
        .NumberFormat = "mm/dd/yy"
        .Value = EndDate
    End With
End Sub

Edit:
Actually your Dim statement is wrong, it should be:
Dim StartDate As Date, EndDate As Date
So amend your Dim statement in your original code (post #1), see what happens.

Thank you!!
the Dim statement was the issue

 

[martipe1]

Dim StartDate, EndDate As Date would mean

StartDate as Variant
EndDate as Date

that is why @Akuini said the statement is wrong

Thank you for your answer!!
yes, that was the issue
Sorry, I couldn't tag your answer as a solution as only one is allowed

 

[Zot]

Thank you for your answer!!
yes, that was the issue
Sorry, I couldn't tag your answer as a solution as only one is allowed

The solution belongs to @Akuini . I was just explaining more detail on the reason why. I see many people declare variables like that which is not correct for VBA but maybe okay for other language.