Table of Contents
Date Format
Changing the date format in Excel VBA is easy. Use the NumberFormat property in Excel VBA to set the desired date format. Below are simple examples and tips to find the right date format.
Enter a Date
To enter a date in Excel use the “/” or “-” characters.
Note: be aware that date formats can vary based on regional settings. This date is in US Format. Months first Days second.
Long Date Format
To change the date format in cell A1 to the long date format place a command button on your worksheet and add the following code line:
Range(“A1”).NumberFormat = “dddd mmmm dd yyyy”
Result when you click the command button on the sheet:
Short Date Format
To change the date format in cell A1 back to the short date format use the following code line:
Range(“A1”).NumberFormat = “m/d/yyyy”
Result when you click the command button on the sheet:
Find the Right Date Format
To find the right date format execute the following steps.
1. Select a cell with a date.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. Pick the Date format you want.
4. Click ‘Custom’ from the Category list.
5. Copy the number format code shown.
Note: the number format code of a date format typically includes combinations of d (days) m (months) and y (years). Make sure to copy only this part if the number format code contains additional elements.
6. Use this number format code in your Excel VBA code.
Range(“A1”).NumberFormat = “d-mmmm”
Result when you click the command button on the sheet:
Use the following codes to quickly format dates in Excel VBA:
|
Format Code |
Explanation |
|---|---|
|
d |
Displays the day as a number without a leading zero (1-31). |
|
dd |
Displays the day as a number with a leading zero (01-31). |
|
ddd |
Displays the day as an abbreviated name (Mon Tue). |
|
dddd |
Displays the full name of the day (Monday Tuesday). |
|
m |
Displays the month as a number without a leading zero (1-12). |
|
mm |
Displays the month as a number with a leading zero (01-12). |
|
mmm |
Displays the month as an abbreviated name (Jan Feb). |
|
mmmm |
Displays the full name of the month (January February). |
|
yy |
Displays the last two digits of the year (24 for 2024). |
|
yyyy |
Displays the full year (2024). |
