Excel has a total of sixteen date formatting styles all of which can be found by right clicking the cell that the date is in and selecting Format Cells from the menu and clicking Date in the Categories. A short date and long date shortcut are also located in the Home Tab, in the Number Formatting Box they can be selected from the drop-down menu.

Date format in excel 1

Also, by selecting the bottom right hand corner of the Number Formatting Box (highlighted) the Format Cells dialog box will appear. 

Date format in excel 2

A custom date format allows users to display a date anyway they want.

  16 Styles of Date Formatting

  1. Day of the week, Month, Days, Year 

    • Monday, January 1, 2018 

  1. Numerical Month / Days

    • 1/1

  2. Numerical Month / Days / Two-digit Year

    • 1/1/18 

  3. Two-Digit Numerical Month / Two-digit Days / Two-digit Year

    • 01/01/18

  4. Days - Abbreviated Month 

    • 1-Jan

  5. Days - Abbreviated Month - Two-digit Year 

    • 1-Jan-18 

  6. Two-digit Days - Abbreviated Month - Two-digit Year

    • 01-Jan-18 

  7. Abbreviated Month - Two-digit Year

    • Jan-18 

  8. Month - Two-digit Year

    • January-18

  9. Month Days , Year

    • January 1, 2018

  10. Numerical Month / Days / Two-digit Year  Time

    • 1/1/18 12:00 AM

  11. Numerical Month / Days / Two-digit Year Military Time

    • 1/1/18 0:00

  12. Initial Month

    • J

  13. Initial Month – Two-digit Year

    • J-18

  14. Numerical Month / Days / Year

    • 1/1/2018

  15. Days – Abbreviated Month - Year

    • 1-Jan-2018

Date format in excel 3

  Default Formatting

To change the default date format, open the computer’s Control Panel. Under Clock, Language and Region, select change date, time and number formats. In this window, you can select the formatting for Short Date, Long Date, Short Time, Long Time and change the First Day of the Week. At the bottom, you can view the Examples of what will be displayed based on your formatting selections.

  Custom Formatting

You can create custom date formats within Excel by first selecting the cell you want to format and opening the format cells window.

Select Date from the Category Menu and chose the date format that most closely resembles the format you are creating. 

Now select Custom from the Category Menu and you will be taken to the Format Code for the date format to use as a starting point. 

In the Type Box make any of the changes you want using code (listed below) and hit enter. The Custom Date Format will appear in the Cell. The custom date code will also be saved under Custom in the Category Menu but will not be saved in the Date Category.

Date format in excel 4

  Code

Months:

m – numerical months (1-12)

mm – numerical two-digit months (01-12)

mmm – abbreviated months (Jan-Dec)

mmmm – months (January-December)

mmmmm – Initial Months (J-D)

Days:

d – days (1-31)

dd – two-digit days (01-31)

ddd – abbreviated days of the week (Sun-Sat)

dddd – days of the week (Sunday-Saturday)

Years:

yy – last two digits of the year (00-99)

yyyy – years (1900-9999)

Time:

h – hours (0-23)

hh – two-digit hours (00-23)

m – minutes (0-59)

mm – two-digit minutes (00-59)

s – seconds (0-59

ss – two-digit seconds (00-59)

h:mm AM/PM – hour and minute and AM or PM (12:00 AM – 11:59PM)