Get Date Year Month Day With Excel Functions

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:

  1. It’s just a jump to the LEFT
  2. And then a step to the RIGHT
  3. 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 Year with LEFT Function
Get Year with LEFT Function

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 Day with RIGHT Function
Get Day with RIGHT Function

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)
Get Month with MID Function
Get Month with MID Function

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.

____________

0 thoughts on “Get Date Year Month Day With Excel Functions”

  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:

    =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.

Leave a Reply to Dave Patton Cancel reply

Your email address will not be published.

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