Short dates coming up in US format if day is less then 13

[jamesp]

Does anyone know why my short date formatting comes up in US format, if the
day is less than 13. My regional setting are UK only and dd/MM/yyyy. The
long date settings are fine (sample code that I have been using is below)

Many thanks

James


Sub testdate()

Cells(1, 1).Value = Now - 2 ' gives correct date and time (10th of
February)
Cells(2, 1).Value = Format(Now - 2, "Long date") ' gives correct date
(10th of February)
Cells(3, 1).Value = Format(Now - 2, "D/MMM/YYYY") ' gives correct
date(10th of February)



Cells(4, 1).Value = Format(Now - 2, "Short date") ' gives wrong date
(2nd of October)
Cells(5, 1).Value = Format(Now - 2, "dd/mm/YYYY") ' gives wrong date
(2nd of October)
Cells(6, 1).Value = Format(Now - 2, "dd/mm/YYYY") ' gives wrong date
(2nd of October)
End Sub

 

[Norman Jones]

Hi James,

Try:

CDate(Format(Now - 2, "dd/mm/yyyy"))

 

[Bob Phillips]

Damn US dates!

I tend to cast dates

Cells(1, 1).Value = Now - 2 ' gives correct date and time (10th of
February)
Cells(2, 1).Value = Format(Now - 2, "Long date") ' gives correct date
(10th of February)
Cells(3, 1).Value = Format(Now - 2, "D/MMM/YYYY") ' gives correct
date(10th of February)

Cells(4, 1).Value = CDate(Format(Now - 2, "Short date")) ' gives wrong
date (2nd of October)
Cells(5, 1).Value = CDate(Format(Now - 2, "dd/mm/YYYY")) ' gives wrong
date (2nd of October)
Cells(6, 1).Value = CDate(Format(Now - 2, "dd/mm/YYYY")) ' gives wrong
date (2nd of October)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[jamesp]

Thanks Norman and Bob

Hi James,

Try:

CDate(Format(Now - 2, "dd/mm/yyyy"))