Cannot apply a consistent DATE format

[harrinho]

Hi fellas,

I'm struggling with something seemingly very simple. I have a date column in a terrible format and I cannot apply a normal date format to all rows. I don't really care if it would be mm/dd/yyyy or dd/mm/yyyy as long as it is the same for every entry. Whatever I tried with functions like RIGHT, TEXT, text to columns, clean formulas and then copy paste back etc didn't work. Alsol, even if I change the date format to the whole column, nothing is changedAny ideas ?

[TABLE="width: 120"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Close Date[/TD]
[/TR]
[TR]
[TD]12/21/2016[/TD]
[/TR]
[TR]
[TD]5/15/2015[/TD]
[/TR]
[TR]
[TD]3/22/2017[/TD]
[/TR]
[TR]
[TD]12/29/2015[/TD]
[/TR]
[TR]
[TD]7/30/2014[/TD]
[/TR]
[TR]
[TD]6/23/2015[/TD]
[/TR]
[TR]
[TD]7/29/2014[/TD]
[/TR]
[TR]
[TD]5/28/2015[/TD]
[/TR]
[TR]
[TD]12-09-15[/TD]
[/TR]
[TR]
[TD]11/24/2014[/TD]
[/TR]
[TR]
[TD]03-08-17[/TD]
[/TR]
[TR]
[TD]7/28/2015[/TD]
[/TR]
[TR]
[TD]11-11-15[/TD]
[/TR]
[TR]
[TD]3/31/2016[/TD]
[/TR]
[TR]
[TD]2/24/2017[/TD]
[/TR]
[TR]
[TD]11/24/2014[/TD]
[/TR]
[TR]
[TD]4/17/2014[/TD]
[/TR]
[TR]
[TD]11/25/2016[/TD]
[/TR]
[TR]
[TD]3/24/2017[/TD]
[/TR]
[TR]
[TD]6/29/2016[/TD]
[/TR]
[TR]
[TD]11-11-15[/TD]
[/TR]
[TR]
[TD]08-01-15[/TD]
[/TR]
[TR]
[TD]3/28/2017[/TD]
[/TR]
[TR]
[TD]07-01-14[/TD]
[/TR]
[TR]
[TD]9/30/2014[/TD]
[/TR]
[TR]
[TD]09-09-14[/TD]
[/TR]
[TR]
[TD]02-02-17[/TD]
[/TR]
[TR]
[TD]9/30/2016[/TD]
[/TR]
[TR]
[TD]09-01-15[/TD]
[/TR]
[TR]
[TD]2/13/2015[/TD]
[/TR]
[TR]
[TD]11-02-16[/TD]
[/TR]
[TR]
[TD]05-06-14


[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Now I can download your file I can see your trouble.

You have a custom format in M2 of yyyy. Use the formula =YEAR(N2) and format the cell as General.