Excel is changing date format incorrectly

  • Thread starter Thread starter Khuli
  • Start date Start date
K

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?
 
Hi,

Am Thu, 27 Nov 2014 14:19:19 +0100 schrieb Claus Busch:
Range("A1") = DateSerial(2014, 9, 1)
Click to expand...

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


Regards
Claus B.
 
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?
Click to expand...

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
 
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.
Click to expand...

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.
 
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.
Click to expand...

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
 
Range("A1") = DateSerial(2014, 9, 1)


Regards
Claus B.
Click to expand...

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.
 
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.
Click to expand...

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.
 
Back
Top