General Formatted Numbers Refuse to Format into Dates

[bencar]

I got dates on excel in general format form. I want to format them to actual dates. I tried formatting them as dates but they stay the same so it doesnt work. I read about delimiter but I tried it and that doesnt work either or maybe I'm not doing it right. ANyway here are the dates:
31.03.14
28.04.15

I want to format them to look like this:
3/31/2014
4/28/2015

How can I do this very simply using delimiter or other method and without any advanced fancy formulas?

.

 

[Fluff]

Sounds like they are not real dates. What happens if you do
=ISNUMBER(A1)
change A1 to a cell with one of those dates.

 

[bencar]

Sounds like they are not real dates. What happens if you do
=ISNUMBER(A1)
change A1 to a cell with one of those dates.

But thats what I said before. Theyre not formatted as dates theyre in general format.

Anyway I used your formula and entered one of the "date" into A1 and I get 'false'.

Is there a way to do this with delimiter? I tried it in delimiter but I dont think I did it right. Or any other method?

 

[Rick Rothstein]

If all of your dates will be after the year 1999, then you can use this...

=DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

 

[bencar]

If all of your dates will be after the year 1999, then you can use this...

=DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

That worked. But where can I read up on this formula to get the hang of what it means?

And isnt there an easier way to do this in delimiter??

 

[bencar]

If all of your dates will be after the year 1999, then you can use this...

=DATE(2000+RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2))

I'm confused by how this formula works. How does each piece of the formula make '31.03.14' turn into '3/31/2014'?

 

[Rick Rothstein]

I'm confused by how this formula works. How does each piece of the formula make '31.03.14' turn into '3/31/2014'?

The DATE function takes three arguments... DATE(Year,Month,Day). The year is derived from the right two digits and 2000 is added to it to make the year a 4-digit number in the 2000's. The month is derived from the middle two digits... the MID function pulls them out by starting at the 4th character position and grabbing 2 characters. The day is derived from the first two characters (leading zeroes are assumed for single digit days) and we get those using the LEFT function.

 

[bencar]

The DATE function takes three arguments... DATE(Year,Month,Day). The year is derived from the right two digits and 2000 is added to it to make the year a 4-digit number in the 2000's. The month is derived from the middle two digits... the MID function pulls them out by starting at the 4th character position and grabbing 2 characters. The day is derived from the first two characters (leading zeroes are assumed for single digit days) and we get those using the LEFT function.

But how does the formula know where to put the year, day and month? For example, the DATE function follows: year, month and day in that order. My date which is '31.03.14' follows day, month and year. Other dates are formatted as: month, year and day... and day, year and month. How does the formula format these dates in its own order? Its very confusing.