FIX Excel Incorrect Date Format | DD/MM/YYYY | MM/DD/YYYY

How to solve excel incorrect date format error, excel not recognizing date format, Convert Wrongly Formatted Date, how to change date format dd/mm/yyy

What is Incorrect Date Format

Many times Excel would not recognize the date format like DD/MM/YYYY or DD-MMM-YYYY etc. Often excel unable to convert data into date format & produces an error. There are three main reasons, First, when users insert Date & not maintain proper format. Second, when data downloaded from ERP or Web, and the date not formatted properly. The third reason is when users convert any TEXT or CSV file to Excel, due to different file formats and conversion, there is a problem in the Date format.

Example of INCORRECT DATE FORMAT

• [message]
• ##bullhorn##  EXCEL FIND, RIGHT, LEFT, MID FUNCTION 
• I always use the EXCEL FIND, RIGHT, LEFT, and MID functions to solve the incorrect date format errors. Find How to use below:-

FIND function

Find function is used to get the number of position of the text which users want to find. For example, if users find for "T" in the word "ACCOUNTING", the FIND FUNCTION will return the 7. 

RIGHT Function

Excel Right function will return the last character or characters or numbers from the last of the string based on the number specified in the RIGHT syntax. For example, if users want to get the last two digits of the "ACCOUNTING" word, it will return "NG". 

LEFT Function

Excel Left function will return the first character or characters or numbers from the first of the string based on the number specified in the LEFT syntax. For example, if users want to get the FIRST two digits of the "ACCOUNTING" word, it will return "AC". 

MID Function

Excel Left function will return the character or characters or numbers from the string based on the number specified in the MID syntax and number counting position specified in the syntax. For example, if users want to get the 5 digits from the 3RD positions of the "ACCOUNTING" word, it will return "COUNT". 

COMBINE RIGHT, LEFT, MID & FIND function

STEP #1. EXTRACT YEAR

Use the LEFT function and first extract year, if the year is the left side of the string.

Use the RIGHT function and extract year, if the year is the right side of the string.

STEP #2. EXTRACT MONTH

STEP #3. EXTRACT DAY

STEP #4. COMBINE LEFT, RIGHT & MID FUNCTION WITH DATE FUNCTION

STEP #5. COMBINE LEFT, RIGHT, MID, FIND & DATE FUNCTION

STEP #6. UPDATE ANY DATE FORMAT WITH TEXT FUNCTION