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.

 

[etaf]

the year is pobably only text and not a date
the Date is actually a number
hence the issue you are having

you could create one new column and put
=IF(LEN(cell)=4,DATE(cell,1,1),cell) where cell is the cell with the date or year in ie A2

This will make any dates with just the Year in as the 1st Jan that year and so now ALL the column will be seen as a Date
now you can filter

 

[Roxie2401]

Wayne,

Thanks, I had thought of something like that and if I understand, I would end up with a column that looks something like this (all year only would show as the 1st of January):

12/15/1957
01/01/1968
01/28/1920
03/14/2001
01/01/1952
01/01/1925
etc.

Unfortunately, what I need is:

12/15/1957
1968
01/28/1920
03/14/2001
1952
1925
etc.

To be specific - this is a listing of the local Cemetery - and some we know the full data (birth date), others only the year and showing January 1 for those with just the year would be misleading.

 

[etaf]

ok
if yo change the format to number
you will see that ALL the correct formated date changes to a number
excel uses the number of days since 1/1/1900 to calculate dates

so when you filter you filter those numbers

BUT the YYYY
is text like ABC
so the filter is using TEXT filter

i'm not sure how to get over that to sort / filter as you want
while i think, hopefully a more experienced person will reply

 

[Fluff]

Hi & welcome to the board.
When you say

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?

Are you referring to the Autofilter?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

a​

[/TD]
[TD]

b​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

12/15/1957​

[/TD]
[TD]

12/15/1957​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1/1/1968​

[/TD]
[TD]

1968​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1/28/1920​

[/TD]
[TD]

1/28/1920​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

3/14/2001​

[/TD]
[TD]

3/14/2001​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1/1/1952​

[/TD]
[TD]

1952​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1/1/1925​

[/TD]
[TD]

1925​

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

text year is ok ?

b1=IF(AND(MONTH(A1)=1,DAY(A1)=1),TEXT(A1,"yyyy"),A1) copy down

format column b format as date

 

[Roxie2401]

I'm not that familiar with the term Autofilter - but I'm guessing that is correct. I have filter arrows at the top of each column and for the Birth column, if I want to find everyone born in a specific year I just enter "1952" and it will find everyone with 1952.

Just out of curiosity, how do you find out what formatting a particular cell is currently using? It appears that for the cells that only currently have the year (yyyy) the format is General; and for those that I have entered the entire date (12/25/1945), the current format is Date. And, with the cells formatted differently, some with either General or Custom yyyy, and others formatted Date, the filter works.

This may be ok if no one ever highlights the entire column and changes all cells to Date or General..........

Maybe I should ask if there is a way to "lock" the format once the data is entered, if I mix them, some General (or Custom) and others Date?

 

[etaf]

how do you find out what formatting a particular cell is currently using?

as mentioned in my post

if yo change the format to number
you will see that ALL the correct formated date changes to a number
excel uses the number of days since 1/1/1900 to calculate dates

 

[etaf]

note the cell with

3/14/2001
​

when change to format > number
will return
36964

Number of days since 1/1/1900

 

[Roxie2401]

as mentioned in my post

It appears that if I right-click on a particular cell and select "Format Cells" the next menu will show the current format for that cell - in my case, some show Date and others show General.

There may be another way, but this seems to show the current format cell-by-cell.

Thanks