Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
I would like to sort dates in a spreadsheet by month and day - not year. How
can I change the format to avoid the year being used while sorting? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
You can't use formatting, you need to use a help column
=MONTH(A1) will return the month's number, assume first date is in A1, copy down as long as needed then select both columns and sort by the help column -- Regards, Peo Sjoblom "lcardwell" wrote in message ... I would like to sort dates in a spreadsheet by month and day - not year. How can I change the format to avoid the year being used while sorting? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
You can't accomplish what you are asking just by changing the cell format.
All a format does is change how the data is displayed. It does not change the actual data in the cell. Therefore, the sort would still be performed on the full date. To do what you are asking you will have to create 2 other columns and use the MONTH function in 1 and the YEAR function in the other. You can then sort on these 2 columns in order to meet your requirement. "lcardwell" wrote: I would like to sort dates in a spreadsheet by month and day - not year. How can I change the format to avoid the year being used while sorting? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
I am not quite sure I understand. In my excel spreadsheet each date is
formatted "1/4/2005" and I want the format to look like "01/04" so that I can sort the column. I do not understand how to use the formula below. Can you write it step by step? "Peo Sjoblom" wrote: You can't use formatting, you need to use a help column =MONTH(A1) will return the month's number, assume first date is in A1, copy down as long as needed then select both columns and sort by the help column -- Regards, Peo Sjoblom "lcardwell" wrote in message ... I would like to sort dates in a spreadsheet by month and day - not year. How can I change the format to avoid the year being used while sorting? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
It doesn't matter how you format the cells, you can't sort on display
(formatting does not change the underlying values and excel date are days since Jan 0 1900). Assume 1/4/2005 is in cell A1, in a for instance B1 put =MONTH(A1) enter it, now select B1 again and move the cursor to the lower right corener of B1, then either double click to copy down the formula or drag down with the mouse, then select both A and B columns and sort by B ascending -- Regards, Peo Sjoblom "lcardwell" wrote in message ... I am not quite sure I understand. In my excel spreadsheet each date is formatted "1/4/2005" and I want the format to look like "01/04" so that I can sort the column. I do not understand how to use the formula below. Can you write it step by step? "Peo Sjoblom" wrote: You can't use formatting, you need to use a help column =MONTH(A1) will return the month's number, assume first date is in A1, copy down as long as needed then select both columns and sort by the help column -- Regards, Peo Sjoblom "lcardwell" wrote in message ... I would like to sort dates in a spreadsheet by month and day - not year. How can I change the format to avoid the year being used while sorting? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to change a date format
Thank You
"lcardwell" wrote: I would like to sort dates in a spreadsheet by month and day - not year. How can I change the format to avoid the year being used while sorting? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Format Problems?? | Excel Discussion (Misc queries) | |||
MS Query Date Format | Excel Discussion (Misc queries) | |||
I cannot change the date format to English canada | Excel Discussion (Misc queries) | |||
How change Excel default date format to something useful | Excel Discussion (Misc queries) | |||
change date function format in footer | Excel Worksheet Functions |