Custom Date Formats in Excel

In addition to the preset options available for formatting dates in Excel, for specific use cases, additional custom date formats can also be created (coded).

All rules for the creation of custom number formats apply. Custom date formats are created and stored only in the active workbook, and they affect only the way a number is displayed and do not affect the underlying value of the number.

 

Pre-defined formats for date and time in Excel are available under the Format Cells, Date and Time categories:

 

Pre-defined date formats are coded and can be modified, or new custom date formats can be created under the Custom category:

 

The following codes can be used:

Years yy, yyyy

Months m, mm, mmm, mmmm, mmmmm

Days d, dd, ddd, dddd, ddddd

Hours h, hh

Minutes m, mm

Seconds s, ss

 

Note: As month and minute codes are the same, context matters. If you use the “m” or “mm” code after the “h” or “hh” code or before the “ss” code, Excel will display minutes. Otherwise, it will display months.

AM/PM time can be coded by adding AM/PM or A/P at the end of the code.

Parts of the second can be shown by adding the decimal point after the second and then the desired number of zeros.

Elapsed time can be shown by enclosing hours, minutes, or seconds in square brackets.

 

In the following example, we will show those format codes applied to the same value:

Note that we are not exactly using elapsed time in an appropriate way here; elapsed hours, minutes, and seconds from the beginning of the Excel era are shown. If we wanted to show 12 elapsed hours, we would format the decimal number 0,5 with the elapsed time format.

 

Also worth noting is that US names for time periods were used in the above example.

If location is not encoded, system conventions will be used.

Other locales can typically be selected, i.e., format can be changed in Locale (location) under Date and Time formats:

 

Underlying this selection are codes such as

[$-en-US]

[$-fr-FR]

[$-de-DE]

[$-ru-RU]

For a detailed list, you can check the available locales under the Date and/or Time format category.

 

In the following example, we will apply those codes to our figures:

Full date and time formats will look like this:

 

The TEXT function can convert a date to text and simultaneously format it using the date format codes demonstrated in this article.

 

Dig deeper:

Date and Time in Excel

Custom Number Formats in Excel