3 Reasons Why Date Format Is Not Changing In Excel

Have you ever tried adjusting the date format in Excel, only to find it stubbornly remains the same? It’s frustrating, isn’t it?

Due to a few underlying factors, Excel’s date formatting can sometimes be trickier than it appears.

In this tutorial, we’ll look at three common reasons why the date format is not changing in Excel and how to address them.

Let’s get started!

Reasons & Solutions for Date Formatting Issues

Excel Recognizes Data as Text

One of the most common reasons why date format issues arise in Excel is because it treats the data as text instead of recognizing it as dates.

This may occur when importing data from another source, such as a CSV file, a web page, or a text file. Another reason could be that the user might have manually set the cell format as ‘Text.’

You can check if this is the case by going to the Format Cells dialog box and, under the Number tab, looking at the Category section.

You might also notice issues such as leading zeroes, additional spaces, or non-standard separators if your dates are treated as text.

Fixing Your Data

There are a couple of ways to handle this issue:

First, you can use the DATEVALUE function, which comes in handy to convert a date in the form of text to an actual date value. The syntax for this function is:

=DATEVALUE(text)

The DATEVALUE function returns a serial number. You’ll need to format the cell with a date format as needed.

Alternatively, you can use the Text to Columns Wizard, which might benefit larger datasets. To do this, follow these steps:

1. Select the range of cells containing the dates recognized as text.
2. Click on the Data tab on the Ribbon.
3. Select Text to Columns.
4. Choose Delimited and click Next.
5. Choose the appropriate delimiter and click Next.
6. Ensure the Date radio button is selected and choose the correct date format (e.g., DMY, MDY).
7. Click Finish.

This method will convert the text-formatted dates into actual date values, allowing you to change the formatting as needed.

Regional Settings Mismatch

Regional settings play a crucial role in Excel’s date formatting. Sometimes, the date format in your Excel file may not match your system’s regional settings, causing a headache.

For instance, it can lead to issues if your system displays dates in MM-DD-YYYY format and your Excel workbook follows the DD-MM-YYYY format.

Solution For Aligning Date Systems

To resolve this issue, try adjusting the settings in both Excel and your Windows or Mac system. Let’s start with Excel:

1. Windows – Navigate to File > Options > Advanced. Scroll down to the “When calculating this workbook” section.
2. Mac – Navigate to Excel > Preferences > Calculation. Under “When calculating workbooks”

Check if the “Use 1904 date system” option is enabled or disabled, and adjust accordingly. This can help synchronize the date formats used in Excel and your system’s regional settings.

Now, let’s move on to your Windows settings:

1. Go to the Control Panel, then click Clock and Region > Region > Formats.
2. Here, you can change the date format to match the one you use in Excel. Just select your preferred format from the dropdown list, and you’re good to go.

And for Mac users:

• Go to System Settings > General > Language & Region
• Update the Date format as needed

Formula Errors

A third reason you might experience issues with date formats not changing in Excel due to formula issues. Let’s break down some common formula-related problems and their solutions.

Formula formatting

When dates are returned from a formula but not formatted as dates. For example, suppose your Excel sheet has a formula that returns a date as text, appearing in cell A1.

First, check whether it’s a number or text using the ISNUMBER and ISTEXT functions in cells B1 and C1, respectively:

Cell B1: =ISNUMBER(A1)
Cell C1: =ISTEXT(A1)

If the ISTEXT function returns TRUE, use the VALUE function to convert the date text to a number.

In cell D1, enter the following formula:

=VALUE(A1)

Now you’ll need to format the number as a date.

• Right-click cell D1 (or CTRL+1 shortcut), then select Format Cells
• In the Format Cells dialog box – click the Date category in the Category list
• Choose a date format from the Type box, and click OK

The date format should now be correctly displayed in cell D1.

Incorrect referencing

If you use a formula like =DATE(2021, A1, B1) to create a date from two cells that contain the month and day values, but A1 or B1 are blank or contain non-numeric values, Excel will return an error value like #VALUE! or #NUM! instead of a date.

Best Practices for Date Formatting in Excel

Here are some best practices for date formatting in Excel to make your life easier.

Custom Date Formats

First, understand that Excel has various date formats for different purposes. You may need to display only the year, or you’d like to show the day of the week.

You can create your own date formats using custom codes, such as adding text labels like Q1 or FY and using different separators like dashes or slashes.

Here’s how to access the custom date format option:

• Right-click and select “Format Cells” or press Ctrl + 1 on the keyboard.
• In the “Format Cells” dialog box, select the “Number” tab.
• From the list on the left, select “Date” to see predefined date formats.
• Select “Custom” from the list on the left for a custom format.

Excel recognizes specific codes that you can use in custom formats. Here are some key codes:

• d: Day of the month as digits; no leading zero for single-digit days.
• dd: Day of the month as digits; with a leading zero for single-digit days.
• ddd: Day of the week as a three-letter abbreviation.
• dddd: Full name of the day of the week.
• m: Month as digits; no leading zero for single-digit months.
• mm: Month as digits; with a leading zero for single-digit months.
• mmm: Month as a three-letter abbreviation.
• mmmm: Full name of the month.
• y: Year as the last digit.
• yy: Year as the last two digits.
• yyyy: Year as four digits.

Examples of Custom Date Formats:

• “mm/dd/yyyy“: Displays date as 09/18/2023.
• “dddd, mmmm dd, yyyy“: Displays date as Monday, September 18, 2023.
• “dd-mmm-yy“: Displays date as 18-Sep-23.

If you want to include literal text within your format, enclose the text in double-quotes. For example: “Day: ” ddd will result in Day: Mon.

Format Painter Tip

An efficient way to change the date format for multiple cells at once is by using the Format Painter tool.

This involves selecting a cell with the desired date format, clicking the Format Painter button on the Home tab, and then dragging over the cells to which you want to apply the same format. 

You can turn off the Format Painter by pressing Esc or clicking the button again.

Shortcuts Tip

For a quick application of a common date format, such as short date, long date, or time, you can use the drop-down list in the Number group on the Home tab.

Select the cells containing the dates, click the drop-down arrow next to the Number Format box, and choose one of the predefined date formats from the list.

Alternatively, you can use Ctrl+Shift+3 keyboard shortcut to apply the Date format. FYI – Ctrl+Shift+2 shows the time format.

Frequently Asked Questions