Excel in Office 365

Hi normboh 

The text to column as suggested by Zack Barresse  will work.

As a variant, you can also use 

=DATEVALUE(SUBSTITUTE($A1,".","-"))

if you will like to use formula.

Cheers.

normboh hello there,

Unfortunately those dates are seen as text by Excel. There is a possibility that it is indeed a date and only formatted to look that way, but I suspect not. The fastest way to check is to remove horizontal alignment from the cell and see which side of the cell it's on - text always aligns from the left by default and numbers always align from the right. If it's already a date, then you're good already, just format as desired. However, if it's text, we need to parse that text to make Excel see it as a date, which internally is referred to as a serial number.

=DATE(RIGHT(A1,2)+2000,LEFT(A1,FIND(".",A1)-1),MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1)-2))

The above formula will parse dates written as text such as:

12.1.14

12.14.14

1.14.14

etc.

This assumes the year will be of this century and not before year 2000, because Excel will want to transform "14" into 1914 instead of 2014. If there is a possibility these dates will spill into last century, we would need a cut-off year to build into the formula.

You could also just say any year greater than 20 should be considered last century. So a date of 1.1.89 would be considered January 1, 1989, and a date of 1.1.19 would be considered January 1, 2019. For that, we would add a test condition to the formula, like this:

=DATE(RIGHT(A1,2)+IF(--RIGHT(A1,2)>YEAR(TODAY())-ROUNDDOWN(YEAR(TODAY()),-3),0,2000),LEFT(A1,FIND(".",A1)-1),MID(A1,FIND(".",A1)+1,LEN(A1)-FIND(".",A1)-2))

Once you have actual dates you can format as desired.

For your second question, while you can have Excel automatically enter a date when you enter a set of numbers, I highly recommend against doing so. This is because dates can come in various formats. Who is to say 12120 should be January 21, 2020, or December 1, 2020? It's a murky area.