Forum Discussion
Date Formatting in Excel
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.
- Thank you. I will try this solution!
4 Replies
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.
- Oops. Should have said Microsoft 365.
