Excel is changing date format incorrectly

[Khuli]

My regional settings are currently for UK, which displays dates as dd/mm/yyyy.

If I create a new workbook, and write this simple code:

Range("A1")="01/09/2014"

it appears in cell A1 as 09/01/2014. Why would it assume I was giving it the date in US format, and how can tell it not to?

 

[Claus Busch]

Hi,

Am Thu, 27 Nov 2014 05:13:46 -0800 (PST) schrieb Khuli:

Range("A1")="01/09/2014"

write
Range("A1") = DateSerial(2014, 9, 1)


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Thu, 27 Nov 2014 14:19:19 +0100 schrieb Claus Busch:

Range("A1") = DateSerial(2014, 9, 1)

or:
Range("A1") = DateValue("01/09/2014")


Regards
Claus B.

 

[GS]

My regional settings are currently for UK, which displays dates as

dd/mm/yyyy.

If I create a new workbook, and write this simple code:

Range("A1")="01/09/2014"

it appears in cell A1 as 09/01/2014. Why would it assume I was giving
it the date in US format, and how can tell it not to?

AFAIK, this is an OS setting! Up to XP Excel's default date format is
dd/mm/yyyy, but since Vista it's been mm/dd/yyyy. That concludes, then,
that this is a 'system' setting for how a date serial gets displayed.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Garry,

Am Thu, 27 Nov 2014 10:26:20 -0500 schrieb GS:

AFAIK, this is an OS setting! Up to XP Excel's default date format is
dd/mm/yyyy, but since Vista it's been mm/dd/yyyy. That concludes, then,
that this is a 'system' setting for how a date serial gets displayed.

if he writes a DateSerial or a DateValue into the sheet the date will be
displayed with system settings.
But in this case the OP tries to write a string into the cell. That
string is in format (VBA speaks american English) mm/dd/yyyy. So d is 09
and m is 01. In his system he has the format dd/mm/yyyy so month and day
change places.
The only way to avoid this behaviour is to write
DateValue("01/09/2012"). That will be recognized as a date and will be
displayed correctly in his settings


Regards
Claus B.

 

[GS]

Hi Garry,

Am Thu, 27 Nov 2014 10:26:20 -0500 schrieb GS:


if he writes a DateSerial or a DateValue into the sheet the date will
be displayed with system settings.
But in this case the OP tries to write a string into the cell. That
string is in format (VBA speaks american English) mm/dd/yyyy. So d is
09 and m is 01. In his system he has the format dd/mm/yyyy so month
and day change places.
The only way to avoid this behaviour is to write
DateValue("01/09/2012"). That will be recognized as a date and will
be displayed correctly in his settings


Regards
Claus B.

Here's what I thought the OP was after...

[a2]=datevalue("27/11/2014")

...in the Immediate Window returns 11/27/2014 on both XP?/Win7. My read
of this post was that I thought the OP wanted to retain the fromat 'as
entered'. Since "01/09/2014 and "09/01/2014" can be interpreted either
way given the numeric values for d/m are valid for both d/m or m/d,
Excel interpret it 'as entered' to be a date.

If either numeric contains a value greater than 12 then Excel treats
the greater s dd. So this...

[a1]="11/27/2014" (dd/mm/yyyy)

gets treated as a valid date, but this...

[a2]="27/11/2014" (mm/dd/yyyy)

...gets treated as text.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Khuli]

Range("A1") = DateSerial(2014, 9, 1)


Regards
Claus B.

Unfortunately my data originates from SQL and is being returned as a text string, so DateSerial is not an option.
It seems bizarre (to me) that Excel does not use my regional settings as a default format for dates.

 

[Claus Busch]

Hi,

Am Fri, 28 Nov 2014 06:58:20 -0800 (PST) schrieb Khuli:

Unfortunately my data originates from SQL and is being returned as a text string, so DateSerial is not an option.
It seems bizarre (to me) that Excel does not use my regional settings as a default format for dates.

your worksheets and bools have your settings. But VBA works with US
standards.
Try that workaround:

Sub Test()
With Range("A1")
.NumberFormat = "@"
.Value = "01/09/2014"
End With
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End Sub


Regards
Claus B.