Format issue - dates, some with yyyy and some with mm/dd/yyyy in same column

[Roxie2401]

We have been keeping a list of item where one column has the date expressed as yyyy. We have some information where we actually have the mm/dd/yyyy, but not always.

When I have some rows with just the yyyy and some with mm/dd/yyyy - the "Filter" does not search/find all entries. Example: If I want to select the year 1952, I only get those with just the year and not those that include the month, day and 1952.

I have tried General, Text and Data - and what I am beginning to dread is that I may actually need three columns (one for Month, one for Day and one for Year) - where, if we have the information, all three columns will have entries but if we only have the year, the month and day column will be empty for that row.

Is there a way to actually have a mixed date format in the same column and have the filter function still search and list correctly?

Hope I described this issue correctly.

Thanks - this is my first forum post.

 

[Roxie2401]

Let me expand on this and ask a follow-up question. (If I need a new post, please let me know.)

Is there a way to find all the cells in a column that are currently formatted as Date ----- if I want to change the Date format for all of them (but not the cells that are formatted as General in the same column), that is select only the cells currently formatted as Date.

Example: Find all cells formatted as Date, (they currently are mm/dd/yyyy) and change them to 14-Mar-2001, etc. dd-Month-yyyy.

 

[Roxie2401]

Let me expand on this and ask a follow-up question. (If I need a new post, please let me know.)

Is there a way to find all the cells in a column that are currently formatted as Date ----- if I want to change the Date format for all of them (but not the cells that are formatted as General in the same column), that is select only the cells currently formatted as Date.

Example: Find all cells formatted as Date, (they currently are mm/dd/yyyy) and change them to 14-Mar-2001, etc. dd-Month-yyyy.

Think I found the answer - Find and Replace Format seems to work ------- Find Format Date 03/14/2001 Replace with Format Date 14-Mar-2001

What I'm not sure about is how to make this only affect the dates in a specific column and not the entire spreadsheet, don't see any Range setting on the Find, but will keep looking.

Thanks for all the very quick, and thoughtful responses today!! Much appreciated.

 

[etaf]

select them all and right click - format - number
as i mentioned the "real" dates will be numbers
for example the date in your post

3/14/2001
will change to a number
​

36964

the entries with just 2014 , 2001 etc
will stay as those numbers

the only way I can see to filter is to have a helper column
and use the formula i suggested
then you can filter and sort and do what you like on the sheet using the helper column
BUT when you want to print out or give to someone else , hide or dont print the helper column just print the normal column
BUT it will be filtered or in the order you want