CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

What is the numberformat code for Short date?

I know that I can write the following to format a number as a date with the appearance yyyy-mm-dd:

Range("A1").NumberFormat = "yyyy-mm-dd"

I know that I can also write:

Range("A1").NumberFormat = "m/d/yyyy"

and all kinds of things.

But is there a way I can set the number format to become a general short date and leave it up to the user's system to decide the exact appearance?

I'm looking for an expression like:

Range("A1").NumberFormat = "Short Date" '(<-This does not work)

Upvotes: 8

Views: 34900

Answers (2)

Dubison
Dubison

Reputation: 768

Please try this

 Format(Range("A1"), "Short Date")

It will return the system short date value.

Upvotes: -5

ignotus
ignotus

Reputation: 658

It is a bit tricky. If you have a look at a cell's properties, format tab, category Date, you will see some formats beginning with an asterisk (*), those respond to the changes of the user locale. Try setting your cell to the first element, short date, then get its numberformat value in the immediate window with ?activecell.NumberFormat. You'll see that it is "m/d/yyyy", so your snippet does the job well. Just try changing your locale setting in control panel, the format of the cell will change accordingly.

Upvotes: 10

Related Questions