How To Date Format In Excel – Date Formation Guide

Do you have any idea about how to change date format in Excel? Using the Excel date formula, you can simply solve this query. Basically, Excel has a total of sixteen date formatting styles, all of which can be found by right-clicking the cell that has the date. And selecting Format Cells from the menu and clicking Date in the Categories will make you able to find different formats. A long date and short date format in Excel shortcut are also located in the Home Tab, in the Number Formatting Box. They can be selected from the drop-down menu.

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

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 

1. • Monday, January 1, 2018 

2. Numerical Month / Days

   • 1/1

3. Numerical Month / Days / Two-digit Year

   • 1/1/18 

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

   • 01/01/18

5. Days – Abbreviated Month 

   • 1-Jan

6. Days – Abbreviated Month – Two-digit Year 

   • 1-Jan-18 

7. Two-digit Days – Abbreviated Month – Two-digit Year

   • 01-Jan-18 

8. Abbreviated Month – Two-digit Year

   • Jan-18 

9. Month – Two-digit Year

   • January-18

10. Month Days , Year

    • January 1, 2018

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

    • 1/1/18 12:00 AM

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

    • 1/1/18 0:00

13. Initial Month

    • J

14. Initial Month – Two-digit Year

    • J-18

15. Numerical Month / Days / Year

    • 1/1/2018

16. Days – Abbreviated Month – Year

    • 1-Jan-2018

 

How To Date Format In Excel 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 choose 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.

 

How To Use 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)

You can change date format in Excel using any of the above given formatting styles. Each style has unique visual appearance that gives your document a next level grace.