Neat Little Excel Date Trick

By: JP Smith | Published: July 16, 2009

Categories: Tips and Tricks

A couple of weeks back, I had a user present an interesting concern to me.  He had some data he needed uploaded in PeopleSoft but, the file he had contained dates in the YYYYMMDD format.  In Excel, I couldn't locate a date format that would convert this to MM/DD/YYYY.  So, off to Google I went.

Lo and behold, I came across a forum post from 2006 which had the answer I needed.  There, a user provided the following solution:

In a column to the right of the imported data, use this formula:
    =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Copy the formula down as far as needed.

(Replace A1 with the appropriate cell reference)

So, thanks to you, anotherhiggins, for helping me out of a jam.