Write date to cell and change date format

1
votes

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 = "äæîðä " & orderID
Range("A3").Value = "äæîðä ì÷åç " & 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)

date value

but the result is :

enter image description here

1
excelvba
docs.microsoft.com/en-us/office/vba/api/… – braX
You can use Format() function. – Harun24HR
Date variable that stored as dd/mm/yyyy - it isn't stored that way because Date variables do not have a format. You should remove your code and replace it with Range("B2").Value = orderDate, and you should apply the date formatting that you want to the cell. – GSerg
I try this all, no one of those options worked – lidorag
@lidorag did you try what was BraX suggested? Range("B2").NumberFormat = "mm/dd/yyyy" and then Range("B2").Value = orderDate. It iwll definitely work – Siddharth Rout

1 Answers

4
votes

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)