Why does this formatting macro not work?

  • Thread starter Thread starter Paul B
  • Start date Start date
P

Paul B

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
 
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
 
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 >
 
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 **
 
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 >
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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.
 
Back
Top