ExcelTutorials

How to convert invalid date formats into valid data formats?

3 Mins read

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 in Excel.

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

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 a 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

15 Excel Data Cleaning Tricks Guidebook

Bored of downloading text heavy / copy-pasted eBooks? If Yes, you will enjoy this guidebook (43 pages) 

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 results 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 the 3rd column (Remarks), so that TRUE and FALSE are sorted in a separate block.

Sort the Data (Remarks)

Excel Vlookup formula – Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

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 sorts 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 the 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 the FlashFill feature can be used to clean the data even faster.

15 Pivot Tables Tricks for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads.

Most Popular Tricks are #3, #7 & #12

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…