Dates Formatting Question - What if you do not know month and/or d

[Guest]

Hello all.

We have a spreadsheet wherein we have a column of dates that need to be
sorted as dates. Unfortunately, on some of them, we only know the month/year
or just the year. So, our column may look as such:

2005
10/2005
01/14/2006
2006
11/2004

We need to be able to sort these by date. Is there a way to format them for
that to happen? (i.e., something like this:

??/??/2005
10/??/2005
01/14/2006
??/??/2006
11/??/2004

To sort like:
11/??/2004
??/??/2005
10/??/2005
??/??/2006
01/14/2006

Thank you in advance for any advice!

Cheers,
Elf

 

[Guest]

Elfmagesty,

If you want your dates to sort properly, you'll need to ensure that they are
entered as Excel-recognized dates. Dates entered as text (or numbers like
"2005") will be difficult to sort (but, would give you the option to keep
your question marks, if those are important to you). See
http://peltiertech.com/Excel/NumberFormats.html for more info on formats.

Based on your example, you want your dates with missing values to sort
BEFORE the dates with known values. The easiest way to do this is if you
simply "assume" and enter the 1st of the month for unknown days, and January
for unknown months, that will cause that to happen. Of course, you'll lose
the visual indication that some of that info was unknown and you'll likely
have a lot of samples in January and/or on the first of the month. Which is
more important for your needs- proper sorting, or indicating what info is
unknown?

BTW - When entering data, Excel treats dates differently depending on how
much info you give it. If you enter 8/2006 into Excel, it automatically
"assumes" you meant the first of the month and (if you have the cell
formatted as date) show 8/1/2006. However, if you only enter "2006", you'll
get a date somewhere in 1905 instead. You'll have to give it month info, at
least, to get it to register in 2006.

Good luck,

 

[Guest]

Elfmagesty,

If you want your dates to sort properly, you'll need to ensure that they are
entered as Excel-recognized dates. Dates entered as text (or numbers like
"2005") will be difficult to sort (but, would give you the option to keep
your question marks, if those are important to you). See
http://peltiertech.com/Excel/NumberFormats.html for more info on formats.

Based on your example, you want your dates with missing values to sort
BEFORE the dates with known values. The easiest way to do this is if you
simply "assume" and enter the 1st of the month for unknown days, and January
for unknown months, that will cause that to happen. Of course, you'll lose
the visual indication that some of that info was unknown and you'll likely
have a lot of samples in January and/or on the first of the month. Which is
more important for your needs- proper sorting, or indicating what info is
unknown?

BTW - When entering data, Excel treats dates differently depending on how
much info you give it. If you enter 8/2006 into Excel, it automatically
"assumes" you meant the first of the month and (if you have the cell
formatted as date) show 8/1/2006. However, if you only enter "2006", you'll
get a date somewhere in 1905 instead. You'll have to give it month info, at
least, to get it to register in 2006.

Good luck,

 

[Guest]

Hello all.

We have a spreadsheet wherein we have a column of dates that need to be
sorted as dates. Unfortunately, on some of them, we only know the month/year
or just the year. So, our column may look as such:

2005
10/2005
01/14/2006
2006
11/2004

We need to be able to sort these by date. Is there a way to format them for
that to happen? (i.e., something like this:

??/??/2005
10/??/2005
01/14/2006
??/??/2006
11/??/2004

To sort like:
11/??/2004
??/??/2005
10/??/2005
??/??/2006
01/14/2006

Thank you in advance for any advice!

Cheers,
Elf

 

[Guest]

Thanks for the info. Yes, I think that's about the only way we can go - is
to just put 01/01/year for any dates that we don't know.

Thanks for your reply.

Cheers,
Elf