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:

Excel Tips - Fixing Dates 1

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

1) Using DATEVALUE

Excel Tips - Fixing Dates 2

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

Excel Tips - Fixing Dates 3

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

Excel Tips - Fixing Dates 3.1

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

Excel Tips - Fixing Dates 4

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:

Excel Tips - Fixing Dates 5.1

4.1) Select “Delimited” in Step 1

Excel Tips - Fixing Dates 5.2

4.2) Check nothing in Step 2

Excel Tips - Fixing Dates 5.3

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

Excel Tips - Fixing Dates 5.4

Finish.  Here’s the result:

Excel Tips - Fixing Dates 5

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

This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

4 Responses to Fixing trouble dates

  1. Pingback: Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition | Chandoo.org - Learn Microsoft Excel Online

  2. Pingback: Dropdown calendar in Excel | wmfexcel

  3. Pingback: Convert an 8-digit number into Excel-recognizable Date | wmfexcel

  4. Pingback: convert text to date - Page 2

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s