Dates in Excel are the bane of my life and probably the bane of your life too, especially when you try, and fail, to sort those dates into order or you want to do a calculation on those dates such as work out everyone’s age and the formula generates an error.

More often than not it’s because the dates aren’t dates. They may look like dates

But under the hood they’re text entries…

24.12.1999

12.04.1992

19.05.1967

To be able to sort those dates or filter on those dates or use those dates in a formula you need to convert them into something Excel understands.

One way to do that is to use the DATEVALUE function but it doesn’t always work.

It depends on the way that the text-based dates have been typed in and quite often it returns #VALUE as the result.

Another way is to use a long winded formula like this one:

=DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2))

But it’s actually possible to convert a date without a formula using the Text-to-Columns feature. Watch the video below to learn more…

You can download a copy of the file I used in the video by clicking through to YouTube. The link for the file is in the YouTube description.