Excel can handle data containing millions of numbers; however, your data is stored based on the type of content used in the dataset. If you enter a date in Excel, it will format the content accordingly. Dates are always stored as positive integers in the 1900 date system. For instance, if you put the date February 3, 2021, the date may appear as 3-Feb, February 3, 2021, 3 February, or 02/03/2021.
It all depends on your settings, and which date format you like to choose. You can convert date format in Excel and for this, you have to learn a few things. In another instance, if you want to put the date in negative numbers you have to switch to the 1904 date system. In the 1904 system, 0 shows January 1, 1904, and -1 shows January -2, 1904.
If the cell format is set to Standard, you will see the date appears as integers. For instance, February 6, 2020, would appear as 43865, it happens because Excel uses date formatting off of January 1, 1900. To understand the phenomena, you need to know the date formats used in Excel in depth, so that you can learn how to convert date formats in Excel according to your demand.
Excel Date Format
Before moving ahead, let’s find out how Excel is designed to store dates and times. If you get to know how it is formed, more than half the query you can solve it easily. Users often expect that Excel already knows the date, time, month, and year, whereas it is not how it works. The cell formatting is the main factor that determines how a date will appear. Otherwise, Excel is capable to store dates as sequential integers.
Date in Excel
As already mentioned, dates are stored as numbers that show the number of days from January 1, 1900, to December 31, 9999 stored as 2958465.
Here, 2 shows 2-Jan-1900, 3 shows 3-Jan-1900, and 42005 is 1-Jan-2015.
Time in Excel
Usually, time is stored as decimals in Excel, between .0 and .99999. it shows a proportion of the day where .0 is 00:00:00 and .99999 is 23:59:59. For instance, 0.25 is 6:00 AM, 0.5 is 12:00 PM, and 0.541655093 is 12:59:59 PM.
Dates & Times in Excel
As Excel stores dates and times in numbers, you will find 1.25 as January 1, 1900, 6:00 AM, 42005.5 as January 1, 2015, 12:00 PM.
Choose from the Date Format List
You can choose date formats in Excel from the date format list. Below you will find the most used formats.
How to Convert Date Format in Excel
Follow the steps given below:
Choose the cells that needed to be formatted.
Press CTRL + 1 or else Command +1
Choose the “Numbers” tab.
Click on the “Date” option given in the categories.
Choose the date format your choose from the “Type” menu.
How to Change Date Format in Excel
Let’s have some date format examples and how you can change them easily.
Using Default Format of Long Date in Excel
In cell A1, you can see an integer and for this number, we need to find the date represented. And the result must be in the long date format.
Follow the steps given below:
Select cell A1 and open the “Number Format” drop-down menu given in the “Home” tab. now, choose the “Long Date” option.
The long date format would be as dd mmmm yyyy. For this reason, number 1 shows the date 01 January 1900 in the long date format. You must remember that short or long date formats appear as set in the “Control Panel.”
Open the Control Panel and click on “Clock, Language, and Region” to convert the date formats. Now, click on “Change date, time, or number formats” and click OK to save changes.
Similarly, number 2 would be 02 January 1900 in the long date format, and number 3 would be 03 January 1900 in the long date format.
How to Create a Custom Excel Date Format Option
Other than the pre-designed formats, you can create a custom date format in Excel as well. for this, let’s understand the procedure by following the steps given below:
Choose the “Custom” option from the “Category” menu.
You will see the previously selected type and remember that the changes you are making will be used only for custom settings. Once the changes are saved, you will see the default settings.
Change the date by adding the right code in the “Type” box.
In case you need to change the date representation to DD/MM/YYYY, simply open the Format Cells option and then Custom.
Now, add DD/MM/YYYY in the given field.
Final Thoughts
The entire post is loaded with information related to date formats used in Excel. You must be highly familiar with the process of converting date formats in Excel after going through this post. Dates and times are information that cannot be changed because they have to be correct all the time. However, you can change the format or style of their representation.
