Date Format in Excel

What is a date in Excel?

In Excel, you can display the same date in many different ways just by changing the date format.

Dates are whole numbers

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

Now if you change the cell's format to Standard, the cell displays 42370.

Date format

Explanation of the numbering

In Excel, a date is the number of days since 01/01/1900 (which is the first date in Excel).

So 42370 is the number of days between the 01/01/1900 and 01/01/2016.

Date format

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

 Customize your date format

You can write the same date in different formats like this:

  • 01/01/2018
  • Jan 2018
  • Mon, 1 Jan 2018

Date parameters

A date has 3 parameters

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

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

  • t for "tag" in German
  • j for "jour" in French

How to customize a date?

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)
  2. In this dialog box, you select 'Custom' in the Category list and write the date format code in 'Type'.

To format a date, you just write the parameter d, m or y different number of times. For example, dd/mm/yyyy will display the 1st of January 2016 as 01 Jan 2016. By entering the codes 1,2,3 or 4 times, the display will be different.

Don't write text in your date ⚠

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.

Different displays in function of the format code

The following document shows you the result on the date in function of the format code used.

Different examples of custom date

Here, you have different examples of display with the same date.

Related posts


Permanent link to this article: https://www.excel-exercise.com/date-format-in-excel/

Leave a Reply

Your email address will not be published.