Many of us get into a situation when the dates in our data are coded as text. Excel does not recognise such text entries as date and we can not use them for any calculation purpose. However you can easily covert them to date by the help of excel formula.

You may have a situation where the text of the date is actually as date but being a text string it is not recognised as date by excel. It could be 12/Oct/2009 in text string. You can easily convert them using Datevalue formula. Assuming you have text in cell A2, the formula you need to enter is =DATEVALUE(A2)

However many time we get data stored like 20091012. Here October 12, 2009 has been stored as First 4 digits as Year, next 2 Digits as Month, next 2 Digits as Day. It becomes little tricky but it can also be converted to date using combination of Mid and Date function of excel.

Assuming you have date stored as 20091012 in cell A3 of you worksheet, you can convert it to date using =DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

Logic behind this formula is devide the text into Year , Month and Day separatly. This is done by using MID function of excel. Once you are able to break text string into three different components of Date. You put these in the Date Function of Excel. Syntex for Excel Date function is DATE(year, month, day).

MID(A3,1,4) = 2009 - This Year Value in Text String

MID(A3,5,2) = 10 - This is Month Value in Text String

MID(A3,7,2) = 12 - This is Day Value in Text String

This is converted into Grand Formula = DATE(MID(A3,1,4),MID(A3,5,2),MID(A3,7,2))

If you see the formula I have just brokenup the text string using MID in combination with Date function.

See picture below to understand it better.

## convert text to date in excel , excel convert text to date, convert text to date, date values