I have this line of code Columns("A:A").NumberFormat = "m/d/yy" which
should format the column as 8/23/03, but when I run it, it formats the
column as 08/23/2003, when I look at the cell format after I run the code it
is custom mm/dd/yyyy.
I recorded a macro changing the format and got this
Columns("A:A").Select
Selection.NumberFormat = "m/d/yy"
When I was recording it the dates changed to the right format but when I ran
the macro it changed them to 08/23/2003, what is going on here? This happens
in any workbook I run the code on. Thanks for any ideas


--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Rocky, the first code I posted was the same as yours, I know it should work
but I tried recording it to make sure it was right, running this on 2000 at
this time

--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Rocky McKinley" wrote in message
...
Hi Paul,

I tried the code and it worked for me. One small side note, you don't
have
to use selection to do most actions in Excel, the following code will
achieve the same thing.
Also Excel 97 does have some strange bugs.

Columns("A:A").NumberFormat = "m/d/yy"

Regards, Rocky McKinley

"Paul B" wrote in message
...

I have this line of code Columns("A:A").NumberFormat = "m/d/yy" which
should format the column as 8/23/03, but when I run it, it formats the
column as 08/23/2003, when I look at the cell format after I run the
code
it
is custom mm/dd/yyyy.
I recorded a macro changing the format and got this
Columns("A:A").Select
Selection.NumberFormat = "m/d/yy"
When I was recording it the dates changed to the right format but when I
ran
the macro it changed them to 08/23/2003, what is going on here? This
happens
in any workbook I run the code on. Thanks for any ideas


--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Tested on xlXP (english) and xl97 (Dutch)

I noted following:
after running your code in Dutch xl97, the cells number format was
"m-d-jj"

so apparently (and not surprisingly) there's some international issue
involved.

to have excel interpret the / as a litteral
precede it with a backslash ..

et voila!


Columns("A:A").NumberFormat = "m\/d\/yy"
For i = 1 To 10
Cells(i, 1) = CLng(Date + i)
Next



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Paul B" wrote:

Rocky, the first code I posted was the same as yours, I know it should
work but I tried recording it to make sure it was right, running this
on 2000 at this time

keepITcool , if I put it like this it works ,Columns("A:A").NumberFormat =
"m\/d\/yy" , but I can't understand why this works
Columns("A:A").NumberFormat = "m/d" but this does not
Columns("A:A").NumberFormat = "m/d/yy"? Thanks


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"keepitcool" wrote in message
...
Tested on xlXP (english) and xl97 (Dutch)

I noted following:
after running your code in Dutch xl97, the cells number format was
"m-d-jj"

so apparently (and not surprisingly) there's some international issue
involved.

to have excel interpret the / as a litteral
precede it with a backslash ..

et voila!


Columns("A:A").NumberFormat = "m\/d\/yy"
For i = 1 To 10
Cells(i, 1) = CLng(Date + i)
Next



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Paul B" wrote:

Rocky, the first code I posted was the same as yours, I know it should
work but I tried recording it to make sure it was right, running this
on 2000 at this time

1. One thing you know for certain :
the "\" forces the next character to be interpreted as a literal string,
thus avoiding any "intelligence" behind the code Excel uses to
interpret/translate user input.

try your code WITH the backslash and you'll get consistent behaviour.
note D1 will give you standard (user-locale) date.

Sub t()
[a1].NumberFormat = "m\/d\/yy"
[b1].NumberFormat = "m\/d"
[c1].NumberFormat = "d\/yy"
[a1:d1] = #3/5/2003#
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Paul B" wrote:

keepITcool , if I put it like this it works
,Columns("A:A").NumberFormat = "m\/d\/yy" , but I can't understand why
this works Columns("A:A").NumberFormat = "m/d" but this does not
Columns("A:A").NumberFormat = "m/d/yy"? Thanks

keepitcool, thanks for the backslash tip, I don't remember having this
problem before just using the m/d/yy, thanks again

--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"keepitcool" wrote in message
...

1. One thing you know for certain :
the "\" forces the next character to be interpreted as a literal string,
thus avoiding any "intelligence" behind the code Excel uses to
interpret/translate user input.

try your code WITH the backslash and you'll get consistent behaviour.
note D1 will give you standard (user-locale) date.

Sub t()
[a1].NumberFormat = "m\/d\/yy"
[b1].NumberFormat = "m\/d"
[c1].NumberFormat = "d\/yy"
[a1:d1] = #3/5/2003#
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Paul B" wrote:

keepITcool , if I put it like this it works
,Columns("A:A").NumberFormat = "m\/d\/yy" , but I can't understand why
this works Columns("A:A").NumberFormat = "m/d" but this does not
Columns("A:A").NumberFormat = "m/d/yy"? Thanks

I think you got hit by a coincidence.

m/d/yy is what excel uses to represent windows short date format.

If your windows short date format is mm/dd/yyyy, then you you use m/d/yy in
excel, you'll get that short date format.

In xl2002, it's documented at the bottom of the Format|Cells|Number Tab|Date
category:

Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.

And one of the entries with an asterisk is the short date format.



Paul B wrote:

I have this line of code Columns("A:A").NumberFormat = "m/d/yy" which
should format the column as 8/23/03, but when I run it, it formats the
column as 08/23/2003, when I look at the cell format after I run the code it
is custom mm/dd/yyyy.
I recorded a macro changing the format and got this
Columns("A:A").Select
Selection.NumberFormat = "m/d/yy"
When I was recording it the dates changed to the right format but when I ran
the macro it changed them to 08/23/2003, what is going on here? This happens
in any workbook I run the code on. Thanks for any ideas

--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

--

Dave Peterson

Dave, thanks for the explanation

--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
"Dave Peterson" wrote in message
...
I think you got hit by a coincidence.

m/d/yy is what excel uses to represent windows short date format.

If your windows short date format is mm/dd/yyyy, then you you use m/d/yy
in
excel, you'll get that short date format.

In xl2002, it's documented at the bottom of the Format|Cells|Number
Tab|Date
category:

Date formats display date and time serial numbers as date values. Except
for
items that have an asterisk (*), applied formats do not switch date orders
with
the operating system.

And one of the entries with an asterisk is the short date format.



Paul B wrote:

I have this line of code Columns("A:A").NumberFormat = "m/d/yy" which
should format the column as 8/23/03, but when I run it, it formats the
column as 08/23/2003, when I look at the cell format after I run the
code it
is custom mm/dd/yyyy.
I recorded a macro changing the format and got this
Columns("A:A").Select
Selection.NumberFormat = "m/d/yy"
When I was recording it the dates changed to the right format but when I
ran
the macro it changed them to 08/23/2003, what is going on here? This
happens
in any workbook I run the code on. Thanks for any ideas

--
Paul B
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

--

Dave Peterson