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

Excel automatically formats date values in the date format. However, if it is not in your system’s date format, Excel will simply recognize it as text. 

Excel aligns its numeric values, including texts to the right. Therefore, if your date value is aligned to the left, it has not been formatted as dates.

While you can change the date according to your system. sometimes you might have to enter dates in a different date format. In such cases, you can use a few workarounds for when your entered date format is not changing in Excel.

KEY TAKEAWAYS

• You can only use hyphen (-) and slash (/) to separate day, month, and year values in dates in Excel.
• Excel follows the system date format.
• You can use Text to Columns to change date formats in Excel.
• You can apply locale-specific date formats using the Format Cells tool.

Copy Date Into New Sheet

If the cell containing your date values has been formatted as text, Excel will not change the values in the date format. The fastest way to revert this change is by copy-pasting the values into a new sheet.

Use Proper Separators

You can enter separate days, months, and years using the slash (/), or the hyphen (-) symbol. If you’ve used any other separator to enter dates, Excel will not register your values as dates. 

If you’ve used a different separator, use the SUBSTITUTE function to replace the separators. Here is how you can enter the SUBSTITUTE function in a formula:

=SUBSTITUTE(text,old_text,new_text,[instance_num])

I have used the period sign (.) as my date separator in my date values in the range A2:A5. However, as it is not a valid separator, Excel is not changing the cell format to dates.

Let’s use the SUBSTITUTE function to replace the period symbol with a hyphen. You can then use flash fill to copy the formula to the rest of the column.

=SUBSTITUTE(A2,".","-")

After you’ve used the formula, change the formula into values. Select your data and copy it (Ctrl + C). Then, click on the same location and select Alt,H,V,V on your keyboard.

Change Date to System Date Format

Once you enter your date in your system’s date format, Excel will immediately identify the date value and convert it into the date formatting.

You can check the date your system uses to enter dates from your system tray. Additionally, you can also use the TODAY function to enter your current date and see the date format your system uses.

Use Text to Columns

If your spreadsheet contains a bigger data set, manually changing these date values to your system date format may sound a bit tedious. In this case, you can use the Text to Columns tool in Excel to change your values into the date format.

In this tool, you will have to specify the arrangement of your days, months, and years. 

1. Select your data.
2. Go to the Data tab and select Text to Columns from the Data Tools section.
   
3. From the first window, select Delimited > Next.
   
4. In the second window, uncheck all options > Next.
   
5. Lastly, under Column data format, choose Date.
6. Next to the flyout, select one of the date formats.
   
7. Click Finish.

Format Cells as Dates

You can also use the format cells window to fix Excel not changing the date format. Using the Format cells window, you can change the arrangement of your month, day, and year in a format that is different from your system date format.

1. Select your cell/range.
2. Click Ctrl + 1.
3. From the sidebar, select Date.
4. Choose a Locale.
   
5. Select your format under Type.
   
6. Click OK.

Use DATE Function

If your day, month, and year values are separated in different columns, you cannot use operators like ampersand and functions like CONCAT to combine them into a single value. This is because the concatenated value will be registered as texts.

Instead, you can use the DATE function to combine day, month, and year into a complete date in Excel. Here is the syntax DATE uses when constructing a formula:

=DATE(year, month, day)

In this spreadsheet, we have the date values in the range A2:A11, month in B2:B11, and year values in C2:C11. Here is how we correctly combine these values into dates in Excel. In cell D2, I entered the following formula and applied it to the rest of the column:

=DATE(C2,B2,A2)

After you’ve entered the formula, be sure to convert the formula as values, 

Change System Date Format

If you’re used to entering dates in a format that is different from your system date format, you should consider changing it. This way, the next time you enter the dates, Excel will automatically format the cell as dates without using the above-mentioned methods.

1. Press on the Windows key to open Start.
2. Type Control Panel and open it.
3. Next to View by, choose Category.
4. Under Clock and Region, select Change date, time, or number formats.
   
5. Set your preferred formats by clicking on the fly-out next to Short Date and Long Date.
   

Why Do I See ### Error?

The ### error in Excel is just a warning from Excel that the cell width is too small for the data the cell currently holds. Use Ctrl + A to select your sheet then, go to Home > Format > Autofit Column Width to adjust the cell width to solve the ### error.