SOLVED

Date Formatting in Excel

Occasional Contributor

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.

4 Replies
Oops. Should have said Microsoft 365.

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

best response confirmed by cbrigham (Occasional Contributor)
Solution
Thank you. I will try this solution!
@hans Vogelear
Worked perfectly! Thanks again.