Convert date format

[mtkalman]

Hey there

I have to transfer dates from a report to another sheet.
The report has the dates in dd.mm.yyyy format, and i need them in yyyy.mm.dd
So far i managed to get them into the desired form, but there comes the problem!
If the day number is 12 or lower the exccel treats that as the month.
for example, if i have 13.02.2016 it works perfectly and gives me 2016.02.13
but if i have 07.02.2017 it gives me 2017.07.02

Worksheets("Aktual").Cells(var, 5).Value = Format(Cells(iRow, 2), "yyyy/mm/dd")

this is the line im usig to get what i need.
any suggestions?

Thank you !

[Paul_Hossler]

Maybe you should brute force it to avoid Regional Settings issues




Option Explicit

Sub test()
Dim s As String, d As Long, m As Long, y As Long

s = "13.02.2016"

d = Left(s, 2)
m = Mid(s, 4, 2)
y = Right(s, 4)

Range("A1").Value = Format(DateSerial(y, m, d), "yyyy.mm.dd")

s = "07.02.2017"

d = Left(s, 2)
m = Mid(s, 4, 2)
y = Right(s, 4)

Range("A2").Value = Format(DateSerial(y, m, d), "yyyy.mm.dd")

End Sub

[snb]

Worksheets("Aktual").Cells(var, 5) = Cdate(Format(Cells(iRow, 2), "yyyy/mm/dd"))

[mtkalman]

Thank you
Seems like brute force works once again :D
With a litle adjustment it does what it has to.
Thanks