Date isssue keeps changing the month and day

[Lextar]

All I'm running in the "Immediate" window is the following;

cells(1,1) = "01/04/2013"
Date appears in the cell as "04/01/2013" but right aligned

cells(1,1) = "23/04/2012"
Date appears in the cell as "23/01/2013" but left aligned

I assume the first instance, Excel sees it as a date, and reformats it hence it's right aligned. The second instance sees it as a string, hence left aligned.

I've checked my Locale settings and its all set as UK date dd/MM/yyyy. I've manually set the cell too, but still the same when I run that line of code.

What am I missing or doing wrong?

[ragulduy]

I think if you enter cells(1,1) = "04/23/2012" it should put it as right aligned but keep the local date setting of dd/mm/yyyy.

[JosephP]

or use a date function

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Lextar]

I know that will fix it, but its very difficult to work with that solution.

This is a snippet of code that I'm trying to do as part of a larger chunk of code and its part of a function called from multiple locations. Making that change would give me a lot of headache to ensure it doesn't break anything.

I've never had an issue with the date within the variables, but as soon as I try to display the value in the cell, this occurs and coding an "exception" isn't what I want to do.

Code scenario
if commencedate = "" then
commencedate = SetQuarterDate(QDate)
cells(x,y).value = commencedate
end if

Bacially if someone hasn't put in a project commence date, it will use another field QDate to set the date.

Hence SetQuarterDate is a function, takes in a string with data "Q1 - 12/13" and sets it to "01/04/13"

even if I use format(commencedate,"dd/MM/yyyy"), the date still gets displayed as "04/01/2013".

I can not change the "SetQuarterDate" function as its called elsewhere. And I rather not create a new function.

[Norie]

They are both strings.

If you want to put a 'real' date in a cell try this.

Please Login or Register  to view this content.

PS Why are you using strings for dates?

[Lextar]

Thanks, cdate fixes it. I responded and then saw the second post.

And I don't know why I'm using string for dates Its used as string all over the code I'm working with.