This happens to me a lot. I open a database and find out that the data under the column Date looks like a date, acts like a date but Excel’s perspective isn’t a date. That basically means that even though a human being looks at the cell and thinks it’s a date, Excel looks at it and identifies it as a string. The impact of this is that if you try to analyze, sort or do any mathematical action on that cell it wouldn’t work as expected. Here are two examples of Excel date issue and how I got it to work.

In this tip I will show a you a few real life examples of how I altered the original data so that it will be set as an Excel date. The keys to do this is to understand the source data format and how you can make the switch systematically.

Example #1 – name of month + year

In this example, the date data is a combination of the name of the month and the year. This is not an Excel formatted date. You can see that in column C I tried to use a formula on the date and received an error result. In this case I wanted to break down the string in column B into Excel entities so that I could create an Excel date. I had to add a helping table in the right (column I:J) to translate the name of the month to month number. Then I used the Vlookup function (insert link) + the Right function (insert link) to find the month#. For the year I used the Left function and finally I used the Date (insert function) to complete the transition. In column G I used the same formula as column C but this time you can see it worked.

Code breakdown for easy copying

Month # (column C) = Vlookup(Right(a10,3),H:i,2,0)

Year # (column D) = Left(a10,4)*1

Excel date (column E) = Date (d10,c10,1)

Example #2 – close but no cigar

In this example, the date data built with . and not / and so although it looks like a date, Excel is not identifying it as a date. In this case I understood the template of the “date” – 2 digits , “.” , 2 digits , “.” , 2-4 digits. Once you understand the template you can break it into parts to rebuild as a date. I used find, len and mid to breakdown the string into parts.

Code breakdown for easy copying

1st “.” within the text (column D) = find(“.”,$b25,1)

2nd “.” within the text (column E) = find(“.”,$b25,D25+1)

String length (column F) = len(b25)

Day (column G) = left(b25,d25-1)*1 , I multiply by one just to make sure it’s transformed as a number

Month (column H) = MID(B25,D25+1,E25-D25-1)*1

Year(column I) =MID(B25,E25+1,F25-E25)*1

Full year (column J) =IF(LEN(I25)=2,I25+2000,I25)

Excel date (column K) =DATE(J25,H25,G25)

These are two examples of what to do if the date isn’t a real date in Excel. Usually you just need to understand the template of the data that you received and build the date on your own. If you have such a problem and can’t figure out how to do this please post a comment below and I will try to help out.