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 😕🤔
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:
- 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 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.
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.
Ashutosh Joshi
Frédéric LE GUEN
Joyce
Aryaan
Mary
www.formale-ontologie.de
คาสิโนออนไลน์
Nitish Kumar H
ATUL
Peter paul
Adriana
Roger
alyssa
rajnish kumar
aniee
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