Fixing trouble dates

Different ways to convert dates stored as text or other unrecognized date formats to real dates in Excel.

From time to time, we work with dates in Excel files.  Well, if dates were input correctly as serial number, everyone is happy.  However, if dates were input as text or other unrecognized date format, it could be really frustrating.

The screenshot below shows you the most common “error” in dates:

Don’t worry!  There are many easy ways to fix these troubles.

1) Using DATEVALUE

2) Using simple mathematics operations (+0, -0, *1, /1)

Pros:

• Easy to use with simple function, or mathematic operations.  Simply put, they both convert a number stored as text back to number.

Cons:

• Do not fix the trouble date because of incorrect date separator used (e.g. dot)
• Do not fix trouble date without date separator
• Need helper cells (i.e. not fixing the source date directly)

3) Using Copy and Paste Special –> Operations Add

This is basically the same as method 2.  First select any blank cell –> COPY –> Paste Special –> Operation Add –> OK

Pros:

• Easy
• No helper cells required.  Fixing the dates directly.

Cons:

• Do not fix the trouble date because of incorrect date separator used (e.g. dot)
• Do not fix trouble date without date separator

 

4) Using Text to Columns

What? Yes, I mean Text to Columns under Data Tab.  Please follow the steps below:

4.1) Select “Delimited” in Step 1

4.2) Check nothing in Step 2

4.3) Select Date: DMY (according to the trouble dates setting)

Finish.  Here’s the result:

Pros:

• HoHoHo… Works for all cases.
• No helper cells required.
• Flexible to cater different date setting.  (see step 3 in Text to Columns)
• Works even without separator in date, e.g. 25122014

Cons:

• Hard to believe it is so simple.

Formula approach in converting 25122104 to 25/12/2014

Read Convert 8-digit number into Excel recognizable date.

Reminder: Whichever method you use, change cell format to Date