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

Roxie2401

New Member
Joined
Feb 23, 2018
Messages
6
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
[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
 
Upvote 0
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?
 
Upvote 0
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

 
Upvote 0
note the cell with
3/14/2001
when change to format > number
will return
36964

Number of days since 1/1/1900
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top