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]

 

[Letto4135]

Should be able to just go to the home tab highlight the row, in the number section tell it to format as short date and it should change them all to have / instead of a mix..

 

[harrinho]

Should be able to just go to the home tab highlight the row, in the number section tell it to format as short date and it should change them all to have / instead of a mix..

Yeah, I've tried that. It changes some of the entries but most of them do nothing. It seems like there is an inherit format or something which I cannot get rid of

 

[MARK858]

You don't say which dates don't convert so I will take it your regional date settings are dd/mm/yyyy. If so try using the formula below, copy/paste as values and delete the original column.

=IFERROR(IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,1)))

 

[harrinho]

You don't say which dates don't convert so I will take it your regional date settings are dd/mm/yyyy. If so try using the formula below, copy/paste as values and delete the original column.

Man you are gold. It worked. I'm attaching the file if you can have a look in case there is a simpler solution associated with regional settings or something.

I just noticed an ever more odd situation regarding the date format. I created Column L from Column K using RIGHT. However, when I turn column L's format into date, it shows a random date back to 09-07-05 (column M). I'm struggling to identify what causes this unreasonable change and I just want to apply date format to column L and display i.e. "2017" as "2017" and not "09-07-05"

Any ideas would be much appreciated


https://goo.gl/3CXBHF

 

[MARK858]

A date is just a number (number of days since 1/1/1900) formatted as a date so 2017 is 1/1/1900 + 2016 days so 9/7/1905.
Try YEAR(A2) Changing the cell to suit as can't download the file at the moment.

 

[harrinho]

A date is just a number (number of days since 1/1/1900) formatted as a date so 2017 is 1/1/1900 + 2016 days so 9/7/1905.
Try YEAR(A2) Changing the cell to suit as can't download the file at the moment.

Oh got it. By doing YEAR(L2) I actually just get 1905 as you mentioned, instead of 2017.

 

[harrinho]

Oh got it. By doing YEAR(L2) I actually just get 1905 as you mentioned, instead of 2017.

Can you please try this link now?

https://goo.gl/dYPjXp

 

[MARK858]

There's nothing wrong with the link I just can't download where I am at the moment.

 

[MARK858]

Oh got it. By doing YEAR(L2) I actually just get 1905 as you mentioned, instead of 2017.

I meant on the converted date not the 2015