Date Format is one of the most important techniques you must know in Excel
- A date is a number. So like any number in Excel, you can apply a specific format
- 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 😕🤔
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
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.
- m 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:
- 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, Go to the Number Tab
- Then select 'Custom' in the Category list
- Write the custom code of your date in the textbox Type.
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.
These articles could be useful to avoid this situation.
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.
02/10/2023 @ 18:05
use the parameter "y" and "m". So 2 functions to do
18/03/2023 @ 07:31
how to chage date format from 2022-04-02 09:47:16 to 01-04-2022
18/03/2023 @ 08:32
dd-mm-yyyy
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
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.
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
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).
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
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!
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.
19/07/2021 @ 17:00
How to convert 19-Jul-2021 to 19-07-2021.... Pls help me out with this
19/08/2021 @ 07:03
With Power Query you can do it
26/09/2020 @ 14:33
2019-02-02 13:45:20
HOW TO CHANGE THIS DATE FORMAT IN NORMAL DATE FORMAT? PLS HELP
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.
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?
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,
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?
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)?
05/02/2020 @ 08:05
how to convert dd:mm:yy change mm:dd:yy
05/02/2020 @ 10:56
Not sure to understand. What do you have in your cells? A text or already a date?
12/12/2019 @ 10:43
Hi
How to convert Jan 29, 2019 to yyyymmdd
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
04/12/2019 @ 20:14
I am looking for a formula that will check the format of the date for a specific format. If the format is not in that specific format, then it will flag the cell with a comment. The date has to be in dd/mm/yyyy. If the date is in any other format, e.g. mm/dd/yyyy or yyyy/mm/dd, then it will get flagged with a comment in that cell to change the date to the correct format.
22/11/2019 @ 18:31
hi there,
Wondering how to set the year to autopopulate? Currently my formulas only recognise 2020 but when you type the date into the formula it sets it to 2019 so the formula wont populate correctly. Can you help?
26/11/2019 @ 15:03
I don't understand what is your issue
07/11/2019 @ 16:03
If I type 1/2/20 it comes up as Feb 1, 2020 - how do I change it to come up as Jan 2, 2020 - or do I have to change the way I enter the date?
26/11/2019 @ 15:07
It is because you should have US setting on your computer. Have a look at this article
https://excel-tutorial.com/comma-or-semicolon-in-excel-formula/
31/10/2019 @ 11:10
Hi.
Problem is, by inserting 02-04-2019, date shown is 04-feb-2019. I want 02-Apr-2019 by writing 02-04-2019 in date column. Is it possible that it auto picks first digit for date, second for month and third for year?
26/11/2019 @ 15:12
It is because you should have US setting on your computer. Have a look at this article to change that
https://excel-tutorial.com/comma-or-semicolon-in-excel-formula/
21/08/2019 @ 07:15
PLS help date format. numeric data is not change in date because change after format cell see this ###############################################################################################################################################################################################################################################################
and change after 3112017
21/08/2019 @ 07:26
There is 2 reasons why a date is ######## : the width of the column is too narrow or for negative time.
07/08/2019 @ 22:29
How do I change the date so it is displayed as Wed 7th?
21/08/2019 @ 07:32
I don't know that one, sorry
08/07/2019 @ 07:11
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,
15/05/2019 @ 05:07
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
18/04/2019 @ 12:45
Hi,
How to convert DD/MM/YYYY into MM/DD/YYYY
24/04/2019 @ 18:51
Hi,
Have a look at this article https://excel-tutorial.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
15/06/2019 @ 04:51
What does 01/01/1900 mean
15/06/2019 @ 08:39
It's the number 1 in date format 😉
04/01/2019 @ 13:08
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
04/01/2019 @ 13:38
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://excel-tutorial.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/
11/12/2018 @ 05:29
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
16/12/2018 @ 14:00
In this article, you have the solution 😉
https://excel-tutorial.com/convert-date-yyyymmdd-to-dd-mm-yyyy-without-formula/
21/09/2018 @ 18:33
Great article. Helped me out.
17/04/2018 @ 01:57
Column does not read as a text
17/04/2018 @ 01:55
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
28/03/2018 @ 07:53
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
28/03/2018 @ 08:25
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.