Date Format in Excel

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

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

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
  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

 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

Date Format in ExcelYou 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.

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)

Click here to read a good post about subtracting in excel.

  • |
  • Leave a Reply

    Your email address will not be published. Required fields are marked *