Date Format Not Changing in Excel? Here’s How to Fix it!

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Why is The Date Format Not Changing in Excel?

Sometimes, you encounter an issue in Excel where the date format is not changing as you expected. This can be frustrating, but it is usually caused by a specific reason. And one that is easily solved! But what gives?

Usually, the date format might not be updating in Excel because the cells in question are already assigned with a different format. In such cases, Excel might be unable to recognize and change the format accordingly. 

More likely than not, if you’ve been sent the file externally, the content you’re trying to format as dates is probably stored as text, preventing Excel from applying the date format of your choice.

While the pre-existing format is usually your issue, another reason for the date format not changing could be related to your regional settings on your computer

Excel relies on your Windows regional settings to interpret and display dates. If the default date format, language, or locale in your control panel doesn’t match the date format in your Excel workbook, it might cause issues when changing it.

Ultimately, returning the kind of date you want comes down to the click of a few buttons and POOF! Problem solved.

How to Alter the Default Date Format in Excel?

To change the default date format in Excel, first select the cell or range of cells you wish to format. Right-click and choose “Format Cells.” You could also use the shortcut keys CTRL + 1. In the “Number” tab, select “Date” from the “Category” list, and choose your preferred format. Click “OK” to apply the changes.

format your dates in Excel with the format settings window

If you don’t see a format you like, you could also select the Custom category and define your format.

How to Change Date Format in Excel Using Formula

It wouldn’t be the tool that it is if Excel didn’t give you more than one way to do things.

Surprise, surprise! You can change the date format using a formula to customize your date display. This can help you better understand and work with dates, especially when dealing with different date formats across regions or systems. Here’s how you can easily change the date format using the TEXT function.

First, select the cell where you want the new date format to appear. Now, type the formula =TEXT(cell_reference, “desired_date_format”), where cell_reference is the cell containing the original date, and desired_date_format represents the new format you want to apply. 

For example, if your original date is in cell A1 and you want to change the format to MM/DD/YYYY, your formula would look like this: 

=TEXT(A1,”MM/DD/YYYY”)

Press Enter, and Excel will display the date in the new format. Or maybe you just want the year? You could use the YEAR formula, but to keep with our example, enter:

=TEXT(A1,”yyyy”)

using a formula to change the date format in excel

This will take your date and return a four-digit year instead.

This method allows you to easily change date formats in Excel without needing VBA or altering your system settings. Remember to adjust the formula as needed for specific date formats like DD/MM/YYYY or long date formats. 

Excel Date Format Not Working Until Click in Cell

Sometimes, when you are working with cells in Excel, you may notice that the date format doesn’t change immediately after applying formatting changes. In fact, it makes you question your sanity when the desired value doesn’t pop up unless you click into the cell.

This issue often occurs if the cells already have a value and you are trying to change the format from text to dates, like changing to a short date or even another number format. You can resolve this by clicking on the cell, which forces Excel to update the format for that area. But to avoid this issue altogether, it’s best to format cells before entering any new data.

Frequently Asked Questions

Why Does Excel Display #### Instead of Date?

Excel displays “####” instead of a date when the column is too narrow to display the entire date format. To fix this, simply resize the column by clicking and dragging the boundary between the column headers or double-clicking the border for auto-fit.

How to Switch Time format in Excel?

To change to time format in Excel:

1. Select the cells containing the time data.
2. Right-click and choose “Format Cells.” Or press CTRL + 1.
3. In the “Number” tab, select “Time” from the “Category” list, and choose your preferred format from the available options.
4. Click “OK” to apply the changes.

Why is Excel Showing Dates as Numbers?

Excel stores dates as serial numbers, each corresponding to a specific number. This may cause dates to appear as numbers when cells are not correctly formatted. To fix this, format the cells as dates following the instructions I laid out above.

How Can I Change Date Format in Excel for Whole Column?

To change the date format for an entire column, click on the column header to highlight the whole column. Then, follow the previous steps of opening the format window, choosing the Date category, and picking your desired format.

What To Do If DATEVALUE Function is Not Working in Excel?

If the DATEVALUE function is not working in Excel, ensure that the text being converted is formatted as a valid date and that the function’s syntax is correct. Alternatively, try using other functions like VALUE or combining TEXT and DATEVALUE to convert text strings to dates more effectively.

Leave a Comment