how to change a date format

L

lcardwell

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?
 
P

Peo Sjoblom

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
 
W

William Horton

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.
 
L

lcardwell

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?
 
P

Peo Sjoblom

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
 
Top