I have a slight problem. It would be easier to post an image but I can't because I don't have 10 reputation. Anyway I have a column with the heading "Birth Date", and dates entered in DD/MM/YYYY format such as 23/10/1987.

The birth dates were entered (not by me) as a text value I think (the format of the cells is "general"). Now I want to calculate the age of the person (in years) at the beginning of 2006. I created another cell and entered the following: 01/01/2006 (DD/MM/YYYY format). I used the DATEDIF function but then realised the dates were not in a date format. I thought Excel would be 'clever enough' to notice that it is a date. Is there an easy way to fix this problem?

  • You can supply the link to an image, and someone with higher rep will probably edit your post to include the image.
    – Excellll
    Apr 7, 2014 at 17:14

2 Answers 2


Select the entire column with the dates stored in them, right click on the selection, choose Format Cells, then set this to date.

They should now be converted to dates, and your formulas will work.


Well the best fix would be to convert your "text dates" into real dates. Obviously I am guessing you don't want to do this manually so try using the "=DATEVALUE" command in a different row or "=DATE" and parse the information in using the "MID" function.

If you need more detailed help how to do this then feel free to ask.

Hope this helps!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .