Fix an Excel Time Warp With LEFT RIGHT MID

rockyhorrorshow Sometimes, working with an Excel data import can be a rocky horror show. This month, I've been working with a client who's 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.
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.

Just a Jump to the Left

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 four digits into column C, to show the year.

Then a Step to the Right

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.

Put Your Hands on Your MID

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.

More Excel Text Functions

To see a few more Excel text functions, and a sample workbook, you can visit Ken Puls' website, and download his Text functions teaching workbook. The workbook explains the LEFT, RIGHT, MID, LEN, and FIND functions.

Watch the LEFT, RIGHT, MID Video

To see the steps for using the LEFT, RIGHT and MID functions in Excel, please watch this short Excel video tutorial.


0 thoughts on “Fix an Excel Time Warp With LEFT RIGHT MID”

  1. 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:


    However it looks like you're intentionally putting the year, month, and day into separate columns for this project.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.