CODEWITHSUNDEEP
excelvba
Anu
Anu

Reputation: 21

Change date format from dd/mm/yyy to dd-mm-yyyy

I have tired to change the date format from dd/mm/yyyy to yyyy-mm-dd. In the front end users entered the date in dd/mm/yyyy format while import the same details to Text file the date format should be printed in yyyy-mm-dd format.

Range("A1").Value = Format(Range("A1").value, "dd/mm/yyyy")

Range("A1").Value = Format(Range("A1").value, "dd/mm/yyyy")

The user has entered the date dd/mm/yyyy in Cell A1. While importing the same to Text file it should be printed as yyyy-mm-dd

Upvotes: 0

Views: 1730

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57673

If your date in A1 is a string/text (and not a real date) …

… you cannot use Format() which only works on real dates and numbers. Therfore you need to analyze the string and create a real date first:

Dim DateString As String
DateString = Range("A1").Value

'split string into 3 parts
Dim DateArr As Variant
DateArr = Split(DateString, "/")

'create a real date out of the 3 parts
Dim RealDate As Date
RealDate = DateSerial(DateArr(2), DateArr(1), DateArr(0))

'write real date into cell
Range("A1").Value = RealDate
'format it in desired format
Range("A1").NumberFormat = "yyyy-mm-dd"

If your date in A1 is a real date (not a string) …

… then you can just format your cell

Range("A1").NumberFormat = "yyyy-mm-dd"

Recommendation

Never use strings for dates. A date has always to be a date format never a string/text. Otherwise you cannot tell if a date is dd/mm/yyyy or mm/dd/yyyy. String dates are evil!

Upvotes: 1

z32a7ul
z32a7ul

Reputation: 3777

You should change the formatting of the cell, too, not just its value:

Range("A1").NumberFormat = "yyyy-mm-dd"

Upvotes: 1

Related Questions