Date conversion in Excel

In the wild and wooly world of data, there are often times that you can benefit from a little data manipulation in both Notepad and Excel.  In this post I’ll show you how to use the Excel formulas LEFT(), RIGHT() AND CONCATENATE() to reformat a date field.

Date Conversion

DATE1

Why any database would show date in this manner is beyond me, but I’ve seen it and worse.  The funny thing is what happens when you try to right click and use “Format Cell” to change it to a proper date:

Data from the far distant future.

Data from the far distant future.

Now why Excel can’t make heads or tails of 08 02 2016 (minus the spaces) is beyond the scope of this post.  Fortunately there is a workaround.  For this specific instance I would use Concatenate, in conjunction with right and left trim functions.  There may be a half dozen other ways to resolve this and if you know of a better way please put it in the comments.  On this blog and in life I’m only equipped to share what I’ve learned by my own experience and experiments!  If you’re observant you noticed that Excel is throwing up the “Numbers stored as Text” error in the date field, this is annotated by the green arrow in the upper left corner of the cell.  If you double click in the cell it will remove the leading zero and convert the cell to a number format, with the result being “8022016” instead of “08022016”.  For the purposes of this example please avoid doing that, as it will not work.  In some cases you will need to convert entire columns to the number format, for example if you needed to sum the column or do any other calculations.  Interestingly enough, just changing the cell type to “Number” will not allow you to sum or do calculations on it.  When I need to change an entire column to text I usually copy the column and paste it into Notepad, then copy from Notepad back into Excel and then it’s been converted.  Okay so now on to the solution.

I create four columns to the right of the offending date column:

DATE2

I put the following formulas in the fields:

Month =LEFT(B2,2)
Day =LEFT(K2,2)
Year =RIGHT(B2,4)
Month-day =RIGHT(B2,6)

Putting these formulas in will yield the following result:

DATE3

As you can see this breaks out the month, day and year into their own columns.  The Month-day column “k” makes it possible to take the leftmost 2 characters to form the day column.  Hopefully this illustration with help you to understand what just happened:

DATE4

After this it’s a simple matter of using the Concatenate function to put it all together, but here I show an alternate method that you can use with Notepad to combine columns.  Start a new column named NEWDATE, and enter the following formula: =CONCATENATE(H2,”/”,I2,”/”,J2)

The result should look something like this:

date5

From here you can copy the new date field, and then right click on the destination field and click “Paste_Values” to insert your new date value.

paste_special

That covers the basics.  If you’d like to see this in a video I have one on my YouTube channel here.

Leave a Reply

Your email address will not be published. Required fields are marked *