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:

  • 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)
  • 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/


19 comments

Skip to comment form

    • rajesh on 21/08/2019 at 07:15
    • Reply

    PLS help date format. numeric data is not change in date because change after format cell see this ###############################################################################################################################################################################################################################################################
    and change after 3112017

    1. There is 2 reasons why a date is ######## : the width of the column is too narrow or for negative time.

    • Shhueyb on 07/08/2019 at 22:29
    • Reply

    How do I change the date so it is displayed as Wed 7th?

    1. I don't know that one, sorry

    • divya on 08/07/2019 at 07:11
    • Reply

    Hi,

    I have data that include the date in format DD- MON-YYY ( 5 JUN 2019) how to extract only dates in Text.

    Sample --> Next followup date changed to 12 Jun 2019 2355 and Follow Type changed to Call RemarksNo response [Changes By ]],Next followup date changed to 18 Jun 2019 0155 and Follow Type changed to Call RemarksCalls rejected / sent an email [Changes By ]],Rejection Multiple Follow up Remarks NO response ],

    There are Suppose 2 dates 12 Jun 2019 & 18 Jun 2019

    How to capture only date details in Next column.

    Regards,

    • Azlan on 15/05/2019 at 05:07
    • Reply

    I have a date in a report that shows like this 05.04.2018. How do I change the report to show date like this 5/4/18.
    Appreciate if you are able to help me.
    Thanks

    • sanat on 18/04/2019 at 12:45
    • Reply

    Hi,

    How to convert DD/MM/YYYY into MM/DD/YYYY

    1. Hi,
      Have a look at this article https://www.excel-exercise.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/
      In you case, select the option MDY to return the date with the format MM/DD/YYYY

      • Aaron on 15/06/2019 at 04:51
      • Reply

      What does 01/01/1900 mean

      1. It's the number 1 in date format 😉

    • mishra on 04/01/2019 at 13:08
    • Reply

    after change in number, its not change in number until i click on each column, so pls suggest how can i change in number for whole list

    1. It's because your date are understand as "Text". If your "Date" is on the left of your cell, it's a Text, if it's on the right, it's a number and you can convert easily your column
      Have a look at this article to convert your dates in 3 clics https://www.excel-exercise.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/

    • Mahabuba on 11/12/2018 at 05:29
    • Reply

    My exel Sheet date are showing I want to change
    2018-10-16  16-Oct-18
    2018-10-30  30-Oct-18
    2018-10-30  30-Oct-18
    2018-10-30  30-Oct-18
    2018-10-16  16-Oct-18
    But I cannot Change 2018-10-16 to 16-Oct-18. Lots of formula i Already Try But not working , Can You provide me Good Solution

    1. In this article, you have the solution 😉
      https://www.excel-exercise.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/

    • Praveen Chawla on 21/09/2018 at 18:33
    • Reply

    Great article. Helped me out.

    • Marie on 17/04/2018 at 01:57
    • Reply

    Column does not read as a text

    • Marie on 17/04/2018 at 01:55
    • Reply

    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

    • Paul Cassidy on 28/03/2018 at 07:53
    • Reply

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