Behind the scenes, Excel stores dates as serial numbers. This means that you can take a cell with the number 1 in it, format it as a date, and you’ll get January 1, 1900. So generally if you get a spreadsheet with dates in it, and you see numbers in the date column, you can just format the numbers as dates (in the Number section on the Home tab of the ribbon), and everything works just fine.
But occasionally you may get a spreadsheet with a number like 91480 in a column like Date of Birth. When you format this as a date you get June 17, 2150. Probably not the date of birth you’re looking for. That’s because 91480 refers to the date 9/14/80 – it has just had the slashes removed.
My original solution to this problem was to use custom formatting, LEFT, RIGHT, MID, DATE, and IF to bring everything together. I love nesting functions, so I thought it was fantastic. But as soon as I started teaching it to someone, it seemed excessively complicated. I thought there must be another way. And there is – it’s a feature in Text to Columns.
So for those of you who don’t think nesting functions is more fun than a crossword, here are instructions on adding formatting when it has been stripped out of a date.
Convert MDDYY to M/DD/YY
- Select the cells that contain the stripped dates.
- On the Data tab on the ribbon, select Text to Columns.
- Click Next to move to Step 2 of the wizard. (It doesn’t matter if you select Fixed or Delimited.)
- Click Next again to move to Step 3 of the wizard.
- In the Column data format section, select Date.
- In the drop down box next to Date, select the format the date is currently in. M=month, D=day, Y=year, so if your number is 91480, you would select MDY. If your number was 20111409, you would pick YDM.
- Click Finish.
Important: Excel will always assume that there are two numbers for the day portion for the date. So even though 10284 could be interpreted as 10/2/84, Excel will return 1/02/84 when it converts the number to a formatted date.