Date Format in Excel

Date Format in Excel
Last Updated on 31/03/2024
Reading time: 3 minutes

Date Format is one of the most important techniques you must know in Excel

  1. A date is a number

    So like any number in Excel, you can apply a specific format

  2. Customize any part of a date

    If you want to display Months in letters, this is possible. You simply have to create your custom date code.

Dates are Integer Numbers

To understand what is a date, the best is to write a number in a cell and change the format

Usually, when you insert a date in a cell it is displayed in the format dd/mm/yyyy or mm/dd/yyyy for USA.

  • Let's say you have the date 02/04/2023 in a cell.
  • If you change the cell's format to General, the cell displays 45018 😕🤔
Format Date and General Format

In Excel, a date is the number of days since 01/01/1900 (the first date in Excel). So, 45018 is the number of days between 01/01/1900 and 02/04/2016.

Date format in the ribbon

Dates can be displayed in different ways using the following 2 options (available in the Number Format dropdown in the main menu):

  • Short Date
  • Long Date
Menu Date Format in the ribbon

How to customize a date in Excel

A date consists of a day, a month, and a year. Excel allows you to change the display of each of these components by changing the format of the cell.

  • d is the code for the day
  • m is the code for the month
  • y is the code for the year

The trick lies in the number of letters you'll write

  • d will write the day as a number, without the 0 for days between 1 and 9
  • dd will write the day as a number with always 2 digits, like 01 for the 1st day of the month
  • ddd will write the day in the abbreviated form of the day of the week (Mon, Tue, Wed, ...)
  • dddd will write the day in full (Monday, Tuesday, Wednesday, ...)

It's the same for the months.

  • to write the digit of the month without a 0 before the number
  • mm to write the month with always 2 digits
  • mmm to write the month in its abbreviated form (Jan, Feb, ...)
  • mmmm to write the month in letter (January, February, ....)

For the years, you can write the value in 2 or 4 digits

  • y or yy to write the year in 2 digits
  • yyy or yyyy to write the year in 4 digits
  • Tip, if you just write the letter e, Excel understands that you want to write the year in 4 digits

In function of your language, the letter could be different:

  • t for "tag" (day) in German
  • j for "jour" (day) in French
  • a for "año" (year) in Spanish

Different examples of custom date

The following document is an embedded workbook. You can visualize the TODAY date in different formats. The code for each date is in column A.

Where to write your custom code?

To customize a date:

  1. Open the dialog box Custom Number (with the shortcut Ctrl + 1 or by clicking on the menu More number formats at the bottom of the number format dropdown)
Menu to open the custom format
  1. In this dialog box, Go to the Number Tab
  2. Then select 'Custom' in the Category list
  3. Write the custom code of your date in the textbox Type.
Dialogue box Custom Number Format

Don't write text in your cell !!!

With dates, one of the most common mistakes is to write text inside the format code (1 January 2016 for example). Never do this in Excel. If you do this, the contents of the cell will be Text and not a number

  • In Excel, text is always displayed on the left of a cell.
  • A number or a date is displayed on the right.
Month writting in letters convert the cell in text

These articles could be useful to avoid this situation.

49 Comments

  1. Lisa
    02/10/2023 @ 16:20

    I am using the =DATEDIF formula. =DATEDIF(P3,D1,"YM"). What format should the cell be in order to return the month & year count. For example, I want to know how many months and years for the date 01/01/2020 thru 10/01/2023.

    Reply

    • Frédéric LE GUEN
      02/10/2023 @ 18:05

      use the parameter "y" and "m". So 2 functions to do

      Reply

Leave a Reply

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

Date Format in Excel

Reading time: 3 minutes
Last Updated on 31/03/2024

Date Format is one of the most important techniques you must know in Excel

  1. A date is a number

    So like any number in Excel, you can apply a specific format

  2. Customize any part of a date

    If you want to display Months in letters, this is possible. You simply have to create your custom date code.

Dates are Integer Numbers

To understand what is a date, the best is to write a number in a cell and change the format

Usually, when you insert a date in a cell it is displayed in the format dd/mm/yyyy or mm/dd/yyyy for USA.

  • Let's say you have the date 02/04/2023 in a cell.
  • If you change the cell's format to General, the cell displays 45018 😕🤔
Format Date and General Format

In Excel, a date is the number of days since 01/01/1900 (the first date in Excel). So, 45018 is the number of days between 01/01/1900 and 02/04/2016.

Date format in the ribbon

Dates can be displayed in different ways using the following 2 options (available in the Number Format dropdown in the main menu):

  • Short Date
  • Long Date
Menu Date Format in the ribbon

How to customize a date in Excel

A date consists of a day, a month, and a year. Excel allows you to change the display of each of these components by changing the format of the cell.

  • d is the code for the day
  • m is the code for the month
  • y is the code for the year

The trick lies in the number of letters you'll write

  • d will write the day as a number, without the 0 for days between 1 and 9
  • dd will write the day as a number with always 2 digits, like 01 for the 1st day of the month
  • ddd will write the day in the abbreviated form of the day of the week (Mon, Tue, Wed, ...)
  • dddd will write the day in full (Monday, Tuesday, Wednesday, ...)

It's the same for the months.

  • to write the digit of the month without a 0 before the number
  • mm to write the month with always 2 digits
  • mmm to write the month in its abbreviated form (Jan, Feb, ...)
  • mmmm to write the month in letter (January, February, ....)

For the years, you can write the value in 2 or 4 digits

  • y or yy to write the year in 2 digits
  • yyy or yyyy to write the year in 4 digits
  • Tip, if you just write the letter e, Excel understands that you want to write the year in 4 digits

In function of your language, the letter could be different:

  • t for "tag" (day) in German
  • j for "jour" (day) in French
  • a for "año" (year) in Spanish

Different examples of custom date

The following document is an embedded workbook. You can visualize the TODAY date in different formats. The code for each date is in column A.

Where to write your custom code?

To customize a date:

  1. Open the dialog box Custom Number (with the shortcut Ctrl + 1 or by clicking on the menu More number formats at the bottom of the number format dropdown)
Menu to open the custom format
  1. In this dialog box, Go to the Number Tab
  2. Then select 'Custom' in the Category list
  3. Write the custom code of your date in the textbox Type.
Dialogue box Custom Number Format

Don't write text in your cell !!!

With dates, one of the most common mistakes is to write text inside the format code (1 January 2016 for example). Never do this in Excel. If you do this, the contents of the cell will be Text and not a number

  • In Excel, text is always displayed on the left of a cell.
  • A number or a date is displayed on the right.
Month writting in letters convert the cell in text

These articles could be useful to avoid this situation.

49 Comments

  1. Lisa
    02/10/2023 @ 16:20

    I am using the =DATEDIF formula. =DATEDIF(P3,D1,"YM"). What format should the cell be in order to return the month & year count. For example, I want to know how many months and years for the date 01/01/2020 thru 10/01/2023.

    Reply

    • Frédéric LE GUEN
      02/10/2023 @ 18:05

      use the parameter "y" and "m". So 2 functions to do

      Reply

Leave a Reply

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