Another date format problem in VBA

[Yamezz]

I have dateserial values that I need to enter back into a worksheet with the correct day/month/year format.
Example: the CashDate variable is 45419. When formatting this manually to a date in Excel, the correct date of 7th of May 2024 is displayed.
However, if I get VBA to do it with = Format(CashDate, "dd/mm/yyyy") I get 5th of July 2024 in the cell, which is not what I asked for. This is not just a display issue - when I manually check by formatting back to a number in Excel I get 45478.

How do I get 45419 to read 07/05/2024 (the 7th day of May, 2024)?

Someone should really drag America kicking and screaming into the 18th century...

 

[FormR]

Hi, just write the variable to the cell without trying to format it, if you specifically need to apply formatting then apply it to the cell and not the variable.

 

[FormR]

For example:

Sub Example()
Dim CashDate As Long
CashDate = 45419
With Range("A1")
    .Value = CashDate
    .NumberFormat = "dd/mm/yyyy"
End With
End Sub

 

[Yamezz]

Thanks FormR