Date Formatting in Excel

[cbrigham]

Hello, I am running Windows 10 Pro on my desktop PC. I have Office 365. I have a spreadsheet in Excel and a column with the date format m/dd/yyyy. Is there a way to change the formatting to only show mm/dd and eliminate the year? Among other data, the date column in this spreadsheet is a list of clients whose Policies were "established" during the month of March & April. The year does not matter. The agent wants to see the month and day only and this column sorted by lowest date (i.e. 3/3, 3/5, 3/30, 4/1, 4/3....). My attempts to sort the column A-Z, produces oldest to newest year but the months are no oldest to newest. I've tried changing the cell formatting (Ctrl + 1), but none of other formats I've tried actually change anything. The cells are not protected. I'm attaching a copy of the column only without the other columns with personal identifying information.

Any help would be much appreciated.

[#2]

Oops. Should have said Microsoft 365.

[#3]

@cbrigham 

The "dates" are left-aligned, indicating that they are text values that look like dates.

I'd start by converting them to real dates:

• Select column A.
• On the Data tab of the ribbon, click Text to Columns.
• Click Next > twice.
• Select Date, then select MDY from the dropdown next to it.
• Click Finish.

Next, create a helper column to the right:

• In B2 enter the formula =TEXT(A2,"mm/dd")
• Fill down.

Finally, sort the entire range (not just column B) on column B.

See the attached version.

[#4 · Accepted solution]

Thank you. I will try this solution!

[#5]

@hans Vogelear
Worked perfectly! Thanks again.