«

»

Date format in Excel

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

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

What is a date in Excel?

Dates are whole numbers

Usually, when you insert a date in a cell, you have this result

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

Date format

01/01/2016 and 42370 are the same value but with different formats

Explanation of the numbering

In Excel, a date is the gap in number of days between the 01/01/1900 (which is the first date in Excel) and your date.

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

Date format

When you have a date in a cell, you can display it in different ways using the following options.

In the ribbon, you have 2 options

  • short date
  • long date

 

 

 

 

 

Don't write text in your date ⚠

With dates, one of the most common mistakes is to write text inside the date (1 January 2016 for example) Never do this in Excel ⛔⛔⛔

If you do this, the contain of the cell is a Text and not a number

  • In Excel, text is always on the left of a cell
  • A number or a date is on the right

 

Customize the date format

Parameters of the dates

A date has 3 parameters

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

In function of your local 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

  • 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 list of formats)
  • In this dialog box, you select in the right-hand side 'Custom' and in 'Type' you write the code of your date.

Now, you just have to write the parameter d, m or y different number of times. By entering the codes 1,2,3 or 4 times, the display will be different.

All the display in function of the different code

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

Different examples of custom date

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

Related articles


Have a look at these other articles that could help you in your work

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


5 comments

Skip to comment form

  1. Praveen Chawla

    Great article. Helped me out.

  2. Marie

    Column does not read as a text

  3. Marie

    I have a date in a report that shows like this February 1st, 2018 13:30pm. It continues this way for every date in February. How do I change the report to show date like this 2/1/18. Hope you are able to help.m

  4. Paul Cassidy

    Hi,

    Our project references are laid out like a date as we use 'year-month-project No that month' but it is just a numbered reference and not a date, how do I get excel not to read as a date.

    00-00-00 is what I want a cell to read.

    How do I set up the cell so that I can enter this reference without it thinking it is a date.

    Regards

    1. Frédéric LE GUEN

      Good morning,
      Select your column with the reference and go to the tab Data>Text to Columns and indicate on the third step that your column is a Text.

Leave a Reply

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