Remove date format?

[Terry Pinnell]

This fragment of my spreadsheet shows a Year column containing cells
formatted in two ways.

https://dl.dropboxusercontent.com/u/4019461/Excel-RemoveDateFormat.jpg

The row shown (and those below) were entered with a custom date format
'mmmm' as I wanted to sort by year. (I was slightly surprised that it
worked OK!) However the rows above have been pasted in from a sheet in
which the years are in General format. My attempt to sort this
combined sheet by Year has failed, as you see.

How can I remove the format from the customised cells, so that *all*
entries in that column are sortable please?

I tried Paste Special > Values but that failed.

I could tediously change all the general entries by editing say 1960
to 1/1/1960, but there has to be an easier way!

 

[Terry Pinnell]

Typo correction:

The row shown (and those below) were entered with a custom date format
'yyyy' as I wanted to sort by year.

 

[Claus Busch]

Hi Terry,

Am Sun, 27 Apr 2014 17:29:58 +0100 schrieb Terry Pinnell:

I could tediously change all the general entries by editing say 1960
to 1/1/1960, but there has to be an easier way!

the above is a possibility.
Or you write in G1:
=IF(C1<>"",YEAR(C1),F1)
and copy down
Then copy the data in column G and paste the values to F


Regards
Claus B.

 

[Terry Pinnell]

Hi Terry,

Am Sun, 27 Apr 2014 17:29:58 +0100 schrieb Terry Pinnell:


the above is a possibility.
Or you write in G1:
=IF(C1<>"",YEAR(C1),F1)
and copy down
Then copy the data in column G and paste the values to F


Regards
Claus B.

Thanks Claus.