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.

How to change the Date Format in Excel

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'.

  • Short Date: The dates are displayed with the default settings but in a shorter form. It usually follows the format m/d/yyyy. For example, 20-06-2022.
  • Long Date: The dates are displayed with the default settings but in a longer form. It usually adds the month's name. For example, 20 June 2022.
How to change the Date Format in Excel

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:

  • First, we must select one or more cells containing the dates we want to format.
    How to change the Date Format in Excel
  • Next, we must go to the Home tab and click the drop-down list under the Number section.
    How to change the Date Format in Excel
  • Lastly, we need to select the desired format (Short or Long) by clicking on it. The corresponding date format will appear instantly within the selected cells or a range.
    How to change the Date Format in Excel

Date to Number format in Excel

The various methods are explained here to find which serial Number displays the particular Date.

1. Format Cell dialog

The Format Cell dialog is used to find the Date of the respective serial Number. The steps to be followed are:

  1. Select the cell containing the Date in the worksheet.
    How to change the Date Format in Excel
  2. Press the Ctrl+1 key, which displays the Format Cell Window. In that, choose the General tab.
How to change the Date Format in Excel

If the user wants to know the serial Number of the particular Date, note the serial Number of the Date and close the window, or if the user needs to display the serial Number of the particular Date, Click OK.

2. Excel DateValue Function

The DATEVALUE () function is used to convert the given Date to its respective serial Number. The steps to be followed are:

Select a new cell, namely A1, and enter the formula as =DATEVALUE("1/1/2023"). Press Enter.

How to change the Date Format in Excel

Default date Format in Excel

While working in Excel, the short and long date format are retrieved from Windows Regional Settings. The asterisk(*) symbol is placed in front of the default date format, which is present in the Format Cell Dialog box. To check the default date format in Excel, the steps to be followed are:

  1. Enter the Date in the cell A1
    How to change the Date Format in Excel
  2. Select the cell A1 and press Ctrl+1. The Format Cell dialog box will open in that the default date format is marked with an asterisk (*) symbol.
    How to change the Date Format in Excel

The default date setting will change in Format Cells if the settings in Control Panel will change.

Changing the default date in Excel

Based on the requirement and data, the default date settings in Excel must be changed. For example, to change the time settings from USA style of Date to UK style to Date, the steps to be followed are:

  1. Open the Control Panel on the computer and click Region and Language. Several options display "Change the date, time, or number format."
    How to change the Date Format in Excel
  2. The Region and Language dialog box will open. In the Format section, choose the desired Region and Language.
    How to change the Date Format in Excel
  3. Select the required Short Date and extended date format from the drop-down list. Click OK. The default setting of the Date will change based on preference.
    How to change the Date Format in Excel

Note: To know the complete description of the dd/mm/yy pattern, click the link "What does the notation mean" under the Date and Time format Section.

If the user wants to know more settings in the date pattern, click the "Additional Settings" button in the lower right corner of the Regional and Language dialog box.

How to change the Date Format in Excel

The Customize Format dialog box will display upon clicking the Additional Settings dialog box. In that, choose the Date tab, where the user can enter the Custom short or long Date in the respective box.

How to change the Date Format in Excel

A quick way to change the Default Date

As previously explained, Excel has two default formats to change the Date. The two types are short and long.

The quick method to change the default date is as follows:

  1. Select the individual cell containing the data which needs to format.
    How to change the Date Format in Excel
  2. In the Home tab, in the Number group, choose the arrow present in the Date Tab. The several date types will be displayed in the drop-down list. In that
  3. Choose the desired date format and click OK.
    How to change the Date Format in Excel

If the user wants many date format options, click the More Number Format in the drop-down list or click the dialog box launcher, which is present near the Number.

How to change the Date Format in Excel

Changing the date format in Excel

The different data format needs to be changed for different types of data. To change the date format in one cell or range of cells, the steps to be followed are:

  1. Select the individual cell containing the data that must be formatted, or select a new cell to insert a date.
  2. Click Ctrl+1, which opens the Format cell dialog box. Another option is to right-click the selected cell and choose the Format cell option.
    How to change the Date Format in Excel
  3. The Format Cell dialog box will appear. Select the Number tab and choose the Date option in the Category List. The Type displays various date formats, choosing the desired date format.
    How to change the Date Format in Excel
  4. The Sample data displays the preview of the selected date format. Click OK to save the changes. Hence the desired date format is set in the worksheet.
    How to change the Date Format in Excel

Note: If the dates are not formatted to desired date format, the Date is in text format. To convert the Date to the desired format, it is first converted to the date format.

Converting the date format to another locale

If the given data set consists of dates that are in American Style(Month/Day/Year) and needs to be converted to European Style(Day/Month/Year), the steps to be followed are:

  1. Enter the Date in the cell range A1:A5
    How to change the Date Format in Excel
  2. Select the cell range A1:A5 and click the Ctrl+1 option, which displays the Format Cell option.
  3. The Format Cell dialog box will open. In that Locale(Location) group, choose the desired locale and Click OK.
    How to change the Date Format in Excel

The desired location's date format will be applied to the data. It is one of the quickest methods to change the location of the data.

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:

  • First, we need to select one or more cells in the worksheet where we want to change or apply the date format. For example, we select cells B2, B3, and B4. Each cell has a different date format, and we need to change them into a specific format by choosing from the advanced list.
    How to change the Date Format in Excel
  • Next, we must press the right-click button via the mouse on the selected cells and choose the 'Format Cells' option.
    How to change the Date Format in Excel
    Alternatively, we can press the keyboard shortcut 'Ctrl + 1' to launch the Format Cells window. However, the numeric '1' must be pressed from the number pad area of the keyboard.
  • In the Format Cells window, we must navigate the Number tab and select the category named 'Date' from the left pane. When we click on the 'Date', we see a list of various date formats under the section 'Type'. It looks like this:
    How to change the Date Format in Excel
  • We need to locate the desired date format and select it accordingly in the list. We also see a preview of the selected format under the section 'Sample' above the 'Type' section. In our case, we select the format d.m.yy.
    How to change the Date Format in Excel
  • Once the desired date format is selected, we must click the OK button. The corresponding date format will appear on all the selected cells, as shown below:
    How to change the Date Format in Excel
    Although the desired date format is applied in the above image, we see a regular date format on the formula bar. The Formula Bar usually displays the date format based on the settings configured in the Windows Control Panel.

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:

  • Like the previous method, we first must select one or more cells to apply the new date format.
  • Next, we must open the Format Cells (Ctrl + 1) dialogue box, similar to the previous method. In the Format Cells window, we must navigate to the 'Custom' category under the 'Number' tab. We must type or enter the desired date format under the 'Type' box using specific formatting date codes.
    How to change the Date Format in Excel
  • Lastly, we must click the OK button to apply the given date format to our cells or a range.

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:

CodeDescriptionExample
dTo display the number of the day without leading zero1 - 31
ddTo display the number of the day with a leading zero01 - 31
dddTo display the name of the day of the week in a shorter formSun, Mon, Tue, etc.
ddddTo display the name of the day of the week in a complete formSunday, Monday, Tuesday, etc.
mTo display the number of the month without leading zero1 - 12
mmTo display the number of the month with a leading zero01 - 12
mmmTo display the name of the month in a shorter formJan, Feb, Mar, etc.
mmmmTo display the name of the month in a complete formJanuary, February, March, etc.
mmmmmTo display the name of the month's first letterJ, F, M etc. However, J stands for January, June and July. Same for others.
yyTo display the last two digits of the year22
yyyyTo display the complete year, all four digits2022

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:

How to change the Date Format in Excel

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.

How to change the Date Format in Excel

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:

  • First, we must select the cells to apply locale (location). Next, we must go to the Format Cells (Ctrl + 1) window.
  • In the Format Cells window, we need to navigate the 'Date' category under the 'Number' tab and locate the drop-down menu under the 'Locale' section. We must select the desired locale from the list and click the OK button to apply changes.
    How to change the Date Format in Excel

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:

CodeLocation
[$-409]English, United States
[$-804]Chinese, China
[$-807]German, Switzerland
[$-1009]English, Canada
[$-804]Bengali, India
[$-407]German, Germany
[$-404]Chinese, Taiwan

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

How to change the Date Format in Excel

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")

How to change the Date Format in Excel

Instead of the date cell, we can also insert the numeric date in the formula directly, i.e.:

=TEXT(44732,"dddd, mmmm dd, yyyy")

How to change the Date Format in Excel

Solutions to the Excel date Problems

Microsoft Excel understands the various date format entered by the user. What happens if the problem related to date format arises? To rectify this problem, the various causes and solutions are explained as follows:

Size of the Cell

Sometimes the date format returns the result as pound signs(#####) in a worksheet which means the size of the cell is not enough to fit the selected date format.

To rectify this error, set the column width according to the column width or double-click the right border of the column, where the cell auto fits the Date, or it can be resized.

Fixing the Negative Date Error

Another cause for the Hash Error(#####) is due to the negative numbers present in the Date. Sometimes formula returns this Type of hashtag error and also occurs if a negative number is present in the date format input.

To display the negative numbers in date format, the steps to be followed are:

Open the worksheet, click File>Options>Advanced. Scroll down to the bottom, and tick the checkbox containing "Use 1904 date system" in "While calculating this workbook."

How to change the Date Format in Excel

Here 0 is 1-Jan-1904;1 is 2-Jan-1904; -1is represented as -2-Jan-1904.

To represent the negative dates, this method is used to calculate the data.

Another method used to display negative numbers is using the TEXT function. The representation is as follows:

How to change the Date Format in Excel

Here the value 1 is entered in cell, and value 2 is entered in cell B1. The value A1 is subtracted from cell B1 using the TEXT and ABS functions and followed by desired date format.

The solution to Date imported as text Values in Excel Worksheet

While importing the data from other files or databases to Excel, sometimes it is imported as text values. Even though it looks like Normal dates, excel considers it a text value and displays the error message.

To rectify this problem, Excel converts the "text dates" to the date format using the DATEVALUE function and the Text to Column feature.

Important Points to Remember

  • After changing the date format, if we see code ##### instead of the proper date, we should ensure that the cell is wide enough to hold the entire date. We can double-click on the right-hand border of the column header to expand the width to fit the entire date correctly.
  • When we import the date in Excel, Excel treats them as TEXT. If there are imported dates in the sheet, we must change the TEXT to DATES before changing its format.
  • It is recommended to use the TODAY() function if we need any cell always to display the current date. However, the date format can be applied based on the choice or requirements.
  • Before changing the date format for the desired cells, we must ensure that the cell does not contain additional text values or symbols. If the date is written with an additional text, the entire cell will be treated as the text cell.
  • If negative numbers are formatted as dates in Excel by default, we must change the Excel settings to fix it. We must select the corresponding cell (s) and navigate to Excel Options (or Excel Settings) > Advanced > Use 1904 date system > OK.





Latest Courses