5 Ways To Fix Date Format Errors In Excel

During the data entry process in Excel, you may encounter date format errors that are not as desired. Let’s find out the causes and 5 ways to fix date format errors in Excel with Buffcom.net in the article below.

Causes of Date Format Errors in Excel

Excel date format errors usually stem from Excel not correctly recognizing the date format, specifically:

• Date format errors after copying data from elsewhere into Excel
• Excel incorrectly recognizing dates, causing the day and month to be reversed (e.g. September 5th recognized as May 9th)

• While working with an Excel file exported from other sources, you may encounter the situation where changing the format of a cell containing a date to “Date” returns incorrect data.

So why does Excel incorrectly recognize date formats? There are two main possibilities:

1. The system is recognizing dates in the format of dd/MM/yy (day/month/year), while your original file or data from where you’re copying/importing data follows the format of MM/dd/yy (month/day/year).
2. The second possibility is that when you sort the date column, all the data is sorted in an incorrect order due to differences in location or language settings (Vietnamese/English), leading to differences in default date formats.

Quick Detection and Filtering of Date Format Errors in Excel

Sometimes in an Excel file with a lot of data, not all date data is incorrectly formatted, but only some due to the data entry and copying process. What you need to do is to check and quickly filter out cells with date format errors in Excel using the Filter function. The steps are as follows:

Step 1: Select the data range to check. At the Data tab, select Filter.

Step 2: Select the data with complete day-month-year and not belonging to any year group (because data with date formats will be sorted by year).

Step 3: Press OK to filter out cells with incorrect date format.

Fixing Errors by Setting the Default Date Format for the Computer

The first thing you need to do before fixing date format errors in Excel is to set the default date format for your computer.

Step 1: Open Control Panel -> go to Clock and Region.

Step 2: Go to the Region settings to set the language display and date/time.

Step 3: The Region window appears, select Additional Settings…

In the Customize Format window, on the Date tab, adjust the format of the date to your preference, whether it be short or long. In the Short date field, choose the format dd-MM-yy (day/month/year). In the Long date field, select the format dddd, MMMM d, yyyy.

Click OK to save.

5 Ways to Fix Date Formatting Errors in Excel Due to Inconsistent Data Entry

Suppose you receive an Excel file with date formatting errors caused by incorrect standard data entry, such as 06-06-2022 or 06.06.2022. How can you quickly fix this formatting error? Tinhocmos will show you 6 ways to fix date formatting errors for each specific case:

Method 1: Use the VALUE Function

The VALUE function can convert any “number-like” text string to a number format, so it can be applied to fix date formatting errors.

The syntax of the VALUE function:

= VALUE(text)

Where text is a reference to the cell that contains your date text string.

Method 2: Use the DATEVALUE Function

The syntax of the DATEVALUE function:

=DATEVALUE(date_text)

Where date_text is a reference to the cell that contains your date text string.

Method 3: Use Find and Replace to Fix Date Formatting Errors Separated by Periods (.)

If the Excel file encounters a date formatting error misunderstood as a decimal number due to the use of periods (.) as separators, the VALUE or DATEVALUE function becomes useless.

To convert the error-prone date format (e.g., 06.06.2022) to the standard short date format (06/06/200), simply select all the error-prone date text and use the Find and Replace function (shortcut key Ctrl + H) to replace periods (.) with slashes (/). Select Replace All and wait for the results.

Excel will detect that your text has become a number and automatically format it into a date. Similarly, you can use the Find and Replace function to fix date formatting errors caused by hyphens (-) by replacing the hyphen with a slash.

Method 4: Use Text To Columns to Fix Date Format Errors Caused by Writing DDMMYY Together

If your date data is a series of numbers written in the following format:

And you want to convert this series of numbers into a date format (e.g. 150802 to 15/08/2002), you can do this using Text to Columns.

Step 1: Select the data range containing the dates you want to fix. On the Data tab, in the Data Tools section, select Text to Columns.

Step 2: The Convert Text to Column Wizard window appears, select Fixed width... -> select Next twice to go to step 3.

Step 3: In the Column Data Format section, select Date, then select the appropriate date format from the droplist. Here, Buffcom.net selects the DMY format (day/month/year). Click Finish to complete.

Method 5: Use the Error Checking Function to Fix Date Format Errors in Excel

If a cell in Excel encounters an error, you will see a green triangle in the top left corner of the cell with an exclamation mark! on the right side of the cell. Clicking on the yellow diamond-shaped icon, Excel will suggest ways to fix the errors.

You can quickly fix all dates by using the error checking feature by selecting all cells containing date text strings before clicking on the exclamation mark on the first selected cell.

Conclusion on How to Fix Date Format Errors in Excel

Through this article, Buffcom.net has helped you understand the causes and ways to fix date format errors in Excel, and introduced 5 ways to fix date format errors for each case caused by the incorrect input process:

Method 1: Use the VALUE function to fix date format errors caused by using a hyphen.
Method 2: Use the DATEVALUE function.
Method 3: Use Find and Replace to fix date format errors separated by a dot (.)
Method 4: Use Text to Columns to fix date format errors caused by writing DDMMYY together.
Method 5: Use the Error Checking function to fix date format errors in Excel.
Wishing you success!

In addition, if your work is closely related to office computing but the software you are using does not meet your necessary requirements, please contact Buffcom.net for support!