Date Format in Excel

Date Format in Excel

Last Updated on 04/10/2022
Reading time: 3 minutes

What is a date in Excel?

A date is a number! And like any number (currency, percentage, decimal, ...), you can customize your date format 👍

Dates are whole numbers

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

Let's say you have the date 01/01/2016 in a cell. 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 (the first date in Excel).

So 42370 is the number of days between 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

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)
Menu to open the custom format
  1. In this dialog box, you select 'Custom' in the Category list and write the date format code in 'Type'.
Dialogue box Custom Number Format

To format a date, you just write the parameter d, m or y a different number of times. For example,

  • dd/mm/yyyy will display 01/01/2016
  • dd mmm yyyy => 01 Jan 2016
  • mmmm yyyy => January 2016
  • dddd dd => Friday 01

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

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.

If you want to display the month in letters, just change the month format of your date.

Different examples of custom date

The following document shows you the same date but in different formats. The code for each date is in column A.

Example date format

Different writing of dates according to the format code

In the following document, you can see the impact of each format on the same date.

Example for each date code

47 Comments

  1. Ashutosh Joshi
    18/03/2023 @ 07:31

    how to chage date format from 2022-04-02 09:47:16 to 01-04-2022

    Reply

    • Frédéric LE GUEN
      18/03/2023 @ 08:32

      dd-mm-yyyy

      Reply

  2. Joyce
    16/08/2022 @ 19:07

    I am attempting to change the month only in a column of already entered dates in the column. For example I have dates as 8/3/2022, 8/6/2022, 8/11/2022. I need to change them to September - 9/3/2022, 9/6/2022, 9/11/2022

    Reply

  3. Aryaan
    09/06/2022 @ 07:00

    Hello

    I want to convert date column with data as 'dd-mon-yy' (e.g. 01-jan-20) to dd/mm/yyyy, but in my example, the year is 1920 instead of 2020.

    When using the Date format in excel without formula, the date 01-jan-20 is being returned as 01/01/2020 while it should be 01/01/1920.

    Any step I am missing for this conversion?

    Thanks.

    Reply

    • Frédéric LE GUEN
      09/06/2022 @ 10:51

      It's by design. If the value is 29 or lower, the year will be 2000. So above 30, the year will be 1900

      Reply

  4. Mary
    22/03/2022 @ 23:21

    I get a report with a number that is one long integer. It is the year then month then date then hour then minute then seconds. Is there a way to get it to be "month/date/year hour:minute"? i.e. 20211017195400 is 10/17/2021 19:54 (the seconds aren't important).

    Reply

  5. Kathy
    21/01/2022 @ 20:12

    can someone figure this out? I have tried but nothing is working.
    I need to convert a date from:
    51998 to 5/1/1998
    91992 9/1/1992

    Reply

  6. www.formale-ontologie.de
    10/11/2021 @ 11:36

    I was suggested this web site by my cousin. I'm not sure whether
    this post is written by him as no one else know such detailed about
    my trouble. You're amazing! Thanks!

    Reply

  7. คาสิโนออนไลน์
    07/11/2021 @ 22:57

    Way cool! Some very valid points! I appreciate you
    penning this write-up and the rest of the website is extremely good.

    Reply

  8. Nitish Kumar H
    19/07/2021 @ 17:00

    How to convert 19-Jul-2021 to 19-07-2021.... Pls help me out with this

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:03

      With Power Query you can do it

      Reply

  9. ATUL
    26/09/2020 @ 14:33

    2019-02-02 13:45:20
    HOW TO CHANGE THIS DATE FORMAT IN NORMAL DATE FORMAT? PLS HELP

    Reply

  10. Peter paul
    01/07/2020 @ 15:50

    Hi
    In excel,the column is in date format only.
    But while generating the xl, it displays the value as "43921".
    How to change the settings to view date in dd/mm/yyyy format while opening the xl itself

    Please help me with this.

    Reply

  11. Adriana
    20/02/2020 @ 05:52

    Hello,
    I am working with some data on excel, which I am pasting from a .txt file.
    I noticed that once the date reached 2/13/2020 12:00:00 AM, all dates lost their format. None of the dates are recognized by excel, and given that I am using them for a formula, the formula is not working either.
    The date/time format is a per specified above.
    I used the fuction VALUE and DATEVALUE without any success. I tried changing the cell format and it did not work either.
    I also tried modifying a date cell with the correct format to 2/13/2020 12:00:00 AM and the cell lost its format.
    This is my first time having this issue with Excel, what about you?

    Reply

  12. Roger
    13/02/2020 @ 18:53

    Hi,

    I need to know how many days are between 2 dates.
    I use the function (in spanis excel) sifecha(A1;A2;"d") and it's works well if A1 and A2 are 13/02/2020 and 12/02/2021 for example. But I have the dates like that: A1 are 13-FEB-2020 and A2 are 12-FEB-2021

    Do you know how to convert 13-FEB-2020 to 13/02/2020? Or what can I do to know the days between 13-FEB-2020 and 12-FEB-2021?

    Many thanks for your help,

    Reply

    • Frédéric LE GUEN
      13/02/2020 @ 19:41

      Hi Roger,
      First, if you want to return the number of days between 2 dates, just subtract the 2 cells =A2-A1
      Second, which version of Excel do you use?

      Reply

  13. alyssa
    13/02/2020 @ 08:46

    Question please. My excel recognizes a 2/13 entry as Feb 1, 2013. How do I restore it to recognize said entry as Feb 13, 2020 (current year)?

    Reply

  14. rajnish kumar
    05/02/2020 @ 08:05

    how to convert dd:mm:yy change mm:dd:yy

    Reply

    • Frédéric LE GUEN
      05/02/2020 @ 10:56

      Not sure to understand. What do you have in your cells? A text or already a date?

      Reply

  15. aniee
    12/12/2019 @ 10:43

    Hi
    How to convert Jan 29, 2019 to yyyymmdd

    Reply

    • Frédéric LE GUEN
      03/01/2020 @ 06:32

      I don't understand. If your first cell Jan 29, 2019 is a number and not a text, you can convert your date with the format you want

      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 04/10/2022

What is a date in Excel?

A date is a number! And like any number (currency, percentage, decimal, ...), you can customize your date format 👍

Dates are whole numbers

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

Let's say you have the date 01/01/2016 in a cell. 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 (the first date in Excel).

So 42370 is the number of days between 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

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)
Menu to open the custom format
  1. In this dialog box, you select 'Custom' in the Category list and write the date format code in 'Type'.
Dialogue box Custom Number Format

To format a date, you just write the parameter d, m or y a different number of times. For example,

  • dd/mm/yyyy will display 01/01/2016
  • dd mmm yyyy => 01 Jan 2016
  • mmmm yyyy => January 2016
  • dddd dd => Friday 01

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

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.

If you want to display the month in letters, just change the month format of your date.

Different examples of custom date

The following document shows you the same date but in different formats. The code for each date is in column A.

Example date format

Different writing of dates according to the format code

In the following document, you can see the impact of each format on the same date.

Example for each date code

47 Comments

  1. Ashutosh Joshi
    18/03/2023 @ 07:31

    how to chage date format from 2022-04-02 09:47:16 to 01-04-2022

    Reply

    • Frédéric LE GUEN
      18/03/2023 @ 08:32

      dd-mm-yyyy

      Reply

  2. Joyce
    16/08/2022 @ 19:07

    I am attempting to change the month only in a column of already entered dates in the column. For example I have dates as 8/3/2022, 8/6/2022, 8/11/2022. I need to change them to September - 9/3/2022, 9/6/2022, 9/11/2022

    Reply

  3. Aryaan
    09/06/2022 @ 07:00

    Hello

    I want to convert date column with data as 'dd-mon-yy' (e.g. 01-jan-20) to dd/mm/yyyy, but in my example, the year is 1920 instead of 2020.

    When using the Date format in excel without formula, the date 01-jan-20 is being returned as 01/01/2020 while it should be 01/01/1920.

    Any step I am missing for this conversion?

    Thanks.

    Reply

    • Frédéric LE GUEN
      09/06/2022 @ 10:51

      It's by design. If the value is 29 or lower, the year will be 2000. So above 30, the year will be 1900

      Reply

  4. Mary
    22/03/2022 @ 23:21

    I get a report with a number that is one long integer. It is the year then month then date then hour then minute then seconds. Is there a way to get it to be "month/date/year hour:minute"? i.e. 20211017195400 is 10/17/2021 19:54 (the seconds aren't important).

    Reply

  5. Kathy
    21/01/2022 @ 20:12

    can someone figure this out? I have tried but nothing is working.
    I need to convert a date from:
    51998 to 5/1/1998
    91992 9/1/1992

    Reply

  6. www.formale-ontologie.de
    10/11/2021 @ 11:36

    I was suggested this web site by my cousin. I'm not sure whether
    this post is written by him as no one else know such detailed about
    my trouble. You're amazing! Thanks!

    Reply

  7. คาสิโนออนไลน์
    07/11/2021 @ 22:57

    Way cool! Some very valid points! I appreciate you
    penning this write-up and the rest of the website is extremely good.

    Reply

  8. Nitish Kumar H
    19/07/2021 @ 17:00

    How to convert 19-Jul-2021 to 19-07-2021.... Pls help me out with this

    Reply

    • Frédéric LE GUEN
      19/08/2021 @ 07:03

      With Power Query you can do it

      Reply

  9. ATUL
    26/09/2020 @ 14:33

    2019-02-02 13:45:20
    HOW TO CHANGE THIS DATE FORMAT IN NORMAL DATE FORMAT? PLS HELP

    Reply

  10. Peter paul
    01/07/2020 @ 15:50

    Hi
    In excel,the column is in date format only.
    But while generating the xl, it displays the value as "43921".
    How to change the settings to view date in dd/mm/yyyy format while opening the xl itself

    Please help me with this.

    Reply

  11. Adriana
    20/02/2020 @ 05:52

    Hello,
    I am working with some data on excel, which I am pasting from a .txt file.
    I noticed that once the date reached 2/13/2020 12:00:00 AM, all dates lost their format. None of the dates are recognized by excel, and given that I am using them for a formula, the formula is not working either.
    The date/time format is a per specified above.
    I used the fuction VALUE and DATEVALUE without any success. I tried changing the cell format and it did not work either.
    I also tried modifying a date cell with the correct format to 2/13/2020 12:00:00 AM and the cell lost its format.
    This is my first time having this issue with Excel, what about you?

    Reply

  12. Roger
    13/02/2020 @ 18:53

    Hi,

    I need to know how many days are between 2 dates.
    I use the function (in spanis excel) sifecha(A1;A2;"d") and it's works well if A1 and A2 are 13/02/2020 and 12/02/2021 for example. But I have the dates like that: A1 are 13-FEB-2020 and A2 are 12-FEB-2021

    Do you know how to convert 13-FEB-2020 to 13/02/2020? Or what can I do to know the days between 13-FEB-2020 and 12-FEB-2021?

    Many thanks for your help,

    Reply

    • Frédéric LE GUEN
      13/02/2020 @ 19:41

      Hi Roger,
      First, if you want to return the number of days between 2 dates, just subtract the 2 cells =A2-A1
      Second, which version of Excel do you use?

      Reply

  13. alyssa
    13/02/2020 @ 08:46

    Question please. My excel recognizes a 2/13 entry as Feb 1, 2013. How do I restore it to recognize said entry as Feb 13, 2020 (current year)?

    Reply

  14. rajnish kumar
    05/02/2020 @ 08:05

    how to convert dd:mm:yy change mm:dd:yy

    Reply

    • Frédéric LE GUEN
      05/02/2020 @ 10:56

      Not sure to understand. What do you have in your cells? A text or already a date?

      Reply

  15. aniee
    12/12/2019 @ 10:43

    Hi
    How to convert Jan 29, 2019 to yyyymmdd

    Reply

    • Frédéric LE GUEN
      03/01/2020 @ 06:32

      I don't understand. If your first cell Jan 29, 2019 is a number and not a text, you can convert your date with the format you want

      Reply

Leave a Reply

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