Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Formatting Question - What if you do not know month and/or d
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Formatting Question - What if you do not know month and/or d
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, "elfmajesty" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Formatting Question - What if you do not know month and/or d
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, "elfmajesty" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Formatting Question - What if you do not know month and/or d
"elfmajesty" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates Formatting Question - What if you do not know month and/
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 "Heidi" wrote: "elfmajesty" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tough conditional formatting question | Excel Discussion (Misc queries) | |||
Calculating revenue per month by aggregating dates | Excel Worksheet Functions | |||
Help with Conditional Formatting (Dates) | Excel Discussion (Misc queries) | |||
Formatting month values | Excel Worksheet Functions | |||
conditional formatting question | Excel Worksheet Functions |