How to convert invalid date formats into valid data formats?

Unclean or Invalid Dates (DMY Format) should be converted to valid data formats.

By default, Excel accepts date input in MM/DD/YY format (US format) unless you change the control panel settings of your PC. Example 22.10.2007 or 22/10/2007 date may be considered invalid.

Now Splitting name into two columns as the First Name and Last Name is easy. Read the Case study and examples based on it. Click now:

Important Note: Technically, every valid date is a number to Excel.

Let’s see how Text to Columns can help clean date formats. Assume you have a recordset consisting of a large number of dates like below:

in case of Invalid Dates should be converted in valid data formats use text to columns in excel
Now look at the very first date – Is it 8th April or 4th August? Confusing, right?

A lot of users select the entire range and then go to Format Cell and then change the format of the Date. But it will not change anything as it is not a valid date format in Excel since dot separators are invalid. If it is not a date, then it’s simple text to excel. Now how will you fix the issue?

From the above table, if you look at the 4th row then you can understand that it is 25th July. To make it a valid date you need to flip the position of 25 with 7 i.e. 7 should be first and then 25. Additionally, the separator should be a slash (/). Will you re-enter dates one by one manually? Very boring task!

Also, now you can, ‘Transfer Single Columnar Data into Multiple Columns. To know more click here

Here’s what you need to do.

Step 1. Add an extra column (Remarks). Then ask a question to Excel if the date given in the first column is a number. You can use ISNUMBER formula. Since every valid date is a number, TRUE indicates, a date is a number and hence, possible a valid date.use ISNUMBER formula

Now after applying ISNUMBER formula, you are getting result either TRUE or FALSE. You don’t need to change the date where you are getting TRUE (read: valid dates).

Step 2. Sort the data table with respect to 3rd column (Remarks), so that TRUE and FALSE are sorted in a separate block.

Sort the Data (Remarks)

Now, this give will you true records on one side and false on another side.

Step 3. Now select only the dates against which the remarks are false.

Step 4. Go To Data tab and then Text to column. Choose a Delimited option. Click on Next

Step 5. Ensure that none of the checkbox is Switched ON in Step 2 of 3. Click on Next.

Step 6. When you are in Step 3 of 3 of Text to Columns wizard, then please stop here for a moment. You have entered the wrong date in Excel, and you need to confess it to Excel. Where is the “confession box”? In Step 3, An option button called Date. Choose the format of the current status/sequence of date format. It is DMY (8.4.2007) in our case. Click on Finish.

Separate data in single column

Step 7: MS Excel will convert the invalid dates into the valid ones. Isn’t it really cool? These sort of date dumps are often seen in ERP reports of SAP, Oracle, Ramco, Tally etc. There are advantages of having a clean date and one such advantage is that you can easily use Pivot Table’s Date grouping feature. Click here to know more about it.

Conclusion:

There are times when there is a lot of information in one text cell and one cannot view all the text in one go. In such cases, splitting the Texts into Columns is a must to know data cleaning technique. This technique basically brings clarity to the text. We have shown you advanced tricks of Text to Columns. This technique can also be used to import data files such as CSV, TXT.

Bonus: An alternative to Text to Columns was introduced in Excel 2013 version. You can check out how FlashFill feature can be used to clean the data even faster.

Now you can convert or fix digits with trailing minus into negative digits. Check out how by clicking here:

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.