excel - Write date to cell and change date format

Question

I have some issue with my VBA code. I got Date variable that stored as dd/mm/yyyy but when I write the date to a specific cell the format change to mm/dd/yyyy, I have been trying many options but none of them worked for me, I also check that the variable know what is the current day, month and year.

The code:

Sheets("report_orders").Select
Range("A1").Value = customerName
Range("A2").Value = "???e? " & orderID
Range("A3").Value = "???e? ì÷?? " & orderPo
'Range("B2").Value = orderDate
Range("B2").Value = Day(orderDate) & "/" & Month(orderDate) & "/" & Year(orderDate)
Debug.Print "year " & Year(orderDate)
Debug.Print "month " & Month(orderDate)
Debug.Print "day " & Day(orderDate)

but the result is :

question from:https://stackoverflow.com/questions/65881901/write-date-to-cell-and-change-date-format

Answer 1

When you put a date into a cell, you can format it using .NumberFormat. Ensure that you format the cell before entering the value. For example

Range("B2").NumberFormat = "mm/dd/yyyy"
Range("B2").Value = orderDate

You can read more about the .NumberFormat in Range.NumberFormat property (Excel)