Sometimes, working with an Excel data import can be a rocky horror (text) show. This month, I’ve been working with a client who is pulling together data from several accounting systems.
The project is extra exciting because each system stores the data in a different format, and we have to assemble it into a common file.
I’m sure you’ve had to deal with a similar challenge, and used your mad scientist Excel skills to clean up the mess.
Imported Data – Date Formats
In one of the import files that my client uses, the date is stored in a YYYYMMDD format.
From that number, we have to calculate the transaction date, so Excel can understand it.
You can use a few Excel functions to extract the year, month and day, and turn that time warp into a valid date.
To quote the “Time Warp” song lyrics:
- It’s just a jump to the LEFT
- And then a step to the RIGHT
- Put your hands on your MID (hips)
Get Year with LEFT Function
In the screen shot below, the imported date is in column A. The year is at the left, in the first four characters.
Use the Excel LEFT function to pull those 4 digits into column C, to show the year.
- =LEFT(A2,4)
Get Day with RIGHT Function
The transaction day is shown in the two characters at the right of the date in column A.
Use the Excel RIGHT function to pull those two digits into column E, to show the day.
- =RIGHT(A2,2)
Get Month with MID Function
The final step is to use the Excel MID function to pull a specific number of characters from the middle of the string in cell A2.
The month number starts at the 5th character, and is 2 characters long.
- =MID(A2,5,2)
More Excel Text Functions
To see a few more Excel text functions, and a sample workbook, you can visit the Split Address With Formulas page on my Contextures website
Also, see the see the Date Format Troubleshooting Tips page, for date troubleshooting tips.
Watch the LEFT, RIGHT, MID Video
To see the steps for using the LEFT, RIGHT and MID functions in Excel, to get a valid data from a date string, you can watch this short Excel video tutorial.
____________
One trick I have always liked is the use of the TEXT function to coerce a number into a date that Excel will recognize. This seems to work:
=TEXT(A2,”0000-00-00?)+0
However it looks like you’re intentionally putting the year, month, and day into separate columns for this project.
Text to Columns would change the text to a proper date.
Thanks for the link, Deb! For reference, if people want to skip the blog page and go right to the download page, the file is at the bottom of this page.
Jason, that’s a cool little trick too!
Dave’s method is fast! I also believe that is a much smarter way of doing it. Your’s was not bad as well.