| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using the sort function for a date column where year was not enter
Dates are always stored including a year.
Use a helper column as sort key. If you have dates like 0101: =month(a1)&day(a1) If you prefer month names: =DATE(1904,Month(a1),day(a1)) and format as mmm dd The use of 1904 is to enable Feb 29 dates -- Kind regards, Niek Otten Microsoft MVP - Excel "Grosvenor" wrote in message ... |I have a "Birthday" column in a spreadsheet where only the month/day is | entered. When sorting on this column, the month/day is sorted by the year | the date was entered even though no year was entered. There appears to be a | defaulting setting somewhere, but cannot determined where to adjust the | setting so the column is sorted on only month/year regardless of year | entered. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using the sort function for a date column where year was not e
This explains everything! Thank you very much!
"Niek Otten" wrote: Dates are always stored including a year. Use a helper column as sort key. If you have dates like 0101: =month(a1)&day(a1) If you prefer month names: =DATE(1904,Month(a1),day(a1)) and format as mmm dd The use of 1904 is to enable Feb 29 dates -- Kind regards, Niek Otten Microsoft MVP - Excel "Grosvenor" wrote in message ... |I have a "Birthday" column in a spreadsheet where only the month/day is | entered. When sorting on this column, the month/day is sorted by the year | the date was entered even though no year was entered. There appears to be a | defaulting setting somewhere, but cannot determined where to adjust the | setting so the column is sorted on only month/year regardless of year | entered. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I enter/sort multiple data within cells of a column? | Excel Discussion (Misc queries) | |||
| Sort by date month and not year | Excel Discussion (Misc queries) | |||
| enter date with year first | Excel Discussion (Misc queries) | |||
| I want to sort by month, date, year. Birthdates. How do I do it | Excel Worksheet Functions | |||
| how can I stop Excel Advancing the year by 1 when I enter a date? | Excel Discussion (Misc queries) | |||
Hybrid Mode
