Date Format in Excel VBA

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:

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:

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.

Result when you click the command button on the sheet:

Use the following codes to quickly format dates in Excel VBA: