Excel imported my dates as day-month instead of year-month. How to fix?

August 26, 2013 7:32 AM

How do I fix this? I "imported" a table from a PDF (big mistake). In the pdf, the dates were formatted as AA-BB, where AA was the month and BB was the year. Excel interpreted that as AA is the month and BB is the day, and gave them all 2013 as the year. So now the date reads as 11-Jan instead of Jan 2011. I no longer have access to the original dataset because I only just noticed the mistake. I am using Excel 2010 on Windows.
posted by rebent to Computers & Internet (6 answers total)

You might have lucked out if Excel is storing them as a date (functionally, Excel stores date as a raw number, day-month vs. year-month is a display consideration). In that case, select the column, right-click, and in the properties menu you can change the date format back.
posted by Hollywood Upstairs Medical College at 7:35 AM on August 26, 2013

Yeah, you want to change the date format from DD MMM to MMM YYYY.
posted by dfriedman at 7:36 AM on August 26, 2013

I've had the same issue with dates imported turning into weird text strings. I fixed it by turning the cells into text format, and then using a text edit program to fix the text string and DateValue to turn it into the correct strings and then making it a date format. Dates as Text is the page I used most recently to show me how to fix this.
posted by viggorlijah at 7:38 AM on August 26, 2013 [1 favorite]

Best answer: It's probably easiest to make some new columns. Call one "year", and put that =DAY(a1), call the next "month" and put that =MONTH(a1), then you can use the year and month as needed.
posted by jeather at 7:41 AM on August 26, 2013 [1 favorite]

Best answer: if Excel has placed the entire date string in a single cell for each line, you can highlight the column, go to the DATA tab, and then select "text-to-columns". you will be able to use this function to separate the date field into three columns (one each for day, month, and year) and then change the format of the affected cells to match your source values.
posted by nadawi at 7:41 AM on August 26, 2013

Response by poster: Thanks for the advice. It looks like this formula, based on jeather's suggestion, has solved my issue

=DATE("20"&DAY(G49),MONTH(G49),1)
posted by rebent at 7:52 AM on August 26, 2013