How to change the Date Format in Excel?
Excel, a powerful spreadsheet software, allows users to record various data types in one or more desired cells across several worksheets. In addition to numbers, and text, we often require entering dates and times in an Excel sheet. No matter how we type the dates in Excel, it automatically picks up the default format as it is set in Excel's settings (or Control Panel).
For example, if the configured date format in the settings is "M/d/yyyy", Excel will format the given or entered date "1st Jan 2021" as "1/1/2021". Excel will keep formatting dates as "M/d/yyyy" until we change the settings in Excel or the date format recorded in an Excel cell. If we change the date format from the settings, the newly given format will be the default format and be followed in the future. Thus, it is better to learn how to change the date format in Excel sheet to change how the dates appear in Excel cells differently.
Note: The date system in Excel for Windows starts from January 1, 1900. However, for Mac, it starts from January 1, 1904. That means we cannot type or enter the dates before the specified starting dates in the respective platforms.
Dates in Excel
Before discussing the methods to change date formats within our Excel sheet, we must know how dates are treated and stored in Excel. Generally, it seems like Excel remembers the given date based on its day, month and year. However, this is not the case in how dates work in Excel.
It is quite unbelievable, but the fact is that a date is nothing but the numbers in Excel. Excel stores and remembers the given date in the form of sequential numbers. However, it enables users to choose between various cell formatting to display the respective number as a valid date, including the date, month and year accordingly.
Numeric Date Format
If there is no specific date format is applied in Excel (or changed from Date to General/Standard), we usually see numbers or integers within the cell with dates recorded. The dates stored as integers typically represent or display the number of days since 01/01/1900 (on Windows) or 01/01/1904 (on Mac).
For example, if we enter any positive number (i.e., 1) in an Excel cell with 'General' formatting and change it to 'Date', it becomes January 1, 1900. It occurs because the integer '1' represents the starting date, i.e., January 1, 1900. Likewise, the number '0' is formatted as January 0, 1900, and calculated as of December 31, 1899. The last date supported in Excel for calculation is December 31, 9999, representing the integer 2958465.
Therefore, if we record June 20, 2022, in General format, it will become 44732 because 44732 days passed from January 1, 1900, to June 20, 2022.
Quick Date Formats
Although we can choose a wide range of date formats in Excel, the most common formats are the 'Short Date' and 'Long Date'.
The short date and long date formats may vary from version to version in Excel, depending on the default settings.
We can quickly choose between these two formats and apply them to the desired cells with dates. For that, we have to execute the below steps:
Changing Date Formats in Excel
No matter if we have the date in numeric form, short or long format, we can always change its format to other desired formats. The following are two commonly used ways to change date formats in an Excel sheet:
Choosing from Advanced Date Formats List
Excel offers a wide range of predefined date formats that can be used on the desired cells immediately. We need to use the 'Format Cells' dialogue box to access the list of advanced date formats. We must execute the below steps to change the date format using the 'Format Cells' option:
Creating Customized Date Formats
If we don't like predefined or standard date formats, we can always create our customized format and use them in the desired cells within the sheet. We must execute the below steps to create our custom date format in Excel:
Before following the above steps and creating a custom date format, we must know the formatting codes required to create the date's structure.
List of Formatting Codes in Excel
The following table has various codes with their description and examples, which we can use to create a custom date format in Excel:
The easiest method to create a desired custom format for the dates is to start with any existing date format in the list. That means we can choose any specific date under the 'Type' box from the 'Date' category, then move to the 'Custom' and change the selected format accordingly.
Let us now implement the above formatting codes and learn to create our customized date format using the sample dataset in an Excel sheet.
Example: Suppose we have the dates in different formats in cells B2, B3, and B4. We want to change the format to a custom format (dddd, mmmm dd, yyyy) for all these cells.
To use our custom date format, we first select all the corresponding cells containing dates in our sheet. Afterwards, we go to the Format Cells window, choose the Custom category and enter our custom format in the Type box. It looks like this:
Since the date preview is right, we click the OK button to apply the same format to all selected cells. The dates appear based on the given format, such as: dddd, mmmm dd, yyyy.
Converting Date Formats to Other Locales/Locations
If we need to display dates for different locations, we must convert the date formats to a different locale. For that, we can execute the below steps:
Apart from this, we can also customize the locale settings for the dates in an Excel cell using the Custom format. We only need to create or select the desired format and prefix a date with the desired locale, meaning that we can add a locale before the custom date format accordingly. However, we must only use the specific locale codes formatted as [$-###].
Some essential codes and their locale are as follows:
There are many more locale codes for various locations on the globe.
Suppose we want to change the date format containing a year-month-day (day of the week) for the location in Germany. So, we must apply the custom format in the following way:
[$-407]dddd, dd. Mmmm yyyy
The above image shows the date in German as we used the locale code of Germany, i.e., [$-407].
Changing the Date Format using the TEXT function
Excel's TEXT function also helps us change or apply the desired Date Format in Excel. The function only requires two arguments and follows the below structure (or syntax):
=TEXT(Value to convert into text, "Formatting code")
The first argument can be represented as a cell containing a date, a numeric form of date, etc. The second argument is the Formatting code that we have already discussed above.
For example, suppose our date (numeric form) is in cell B2, and we need to change its format to: dddd, mmmm dd, yyyy. So, we can use the TEXT function in another cell using the below formula:
=TEXT(B2,"dddd, mmmm dd, yyyy")
Instead of the date cell, we can also insert the numeric date in the formula directly, i.e.:
=TEXT(44732,"dddd, mmmm dd, yyyy")
Important Points to Remember
Next TopicHow to compare two Excel sheet