Menu
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)
202
When you type something like 2/2 in a cell, Excel for the web thinks you're typing a date and shows it as 2-Feb. But you can change the date to be shorter or longer. To see a short date like 2/2/2013, select the cell, and then click Home > Number Format > Short Date.
To format a date in VBA, we use the inbuilt FORMAT function itself. It takes input as the date format and returns the desired format required. The arguments required for this function are the expression and the format type. Formatting date and time are sensitive things in Excel, and the same applies to VBA.
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.
71
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
