SOLVED

Date Formatting in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2247906%22%20slang%3D%22en-US%22%3EDate%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247906%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20running%20Windows%2010%20Pro%20on%20my%20desktop%20PC.%20I%20have%20Office%20365.%20I%20have%20a%20spreadsheet%20in%20Excel%20and%20a%20column%20with%20the%20date%20format%20m%2Fdd%2Fyyyy.%20Is%20there%20a%20way%20to%20change%20the%20formatting%20to%20only%20show%20mm%2Fdd%20and%20eliminate%20the%20year%3F%20Among%20other%20data%2C%20the%20date%20column%20in%20this%20spreadsheet%20is%20a%20list%20of%20clients%20whose%20Policies%20were%20%22established%22%20during%20the%20month%20of%20March%20%26amp%3B%20April.%20The%20year%20does%20not%20matter.%20The%20agent%20wants%20to%20see%20the%20month%20and%20day%20only%20and%20this%20column%20sorted%20by%20lowest%20date%20(i.e.%203%2F3%2C%203%2F5%2C%203%2F30%2C%204%2F1%2C%204%2F3....).%20My%20attempts%20to%20sort%20the%20column%20A-Z%2C%20produces%20oldest%20to%20newest%20year%20but%20the%20months%20are%20no%20oldest%20to%20newest.%20I've%20tried%20changing%20the%20cell%20formatting%20(Ctrl%20%2B%201)%2C%20but%20none%20of%20other%20formats%20I've%20tried%20actually%20change%20anything.%20The%20cells%20are%20not%20protected.%20I'm%20attaching%20a%20copy%20of%20the%20column%20only%20without%20the%20other%20columns%20with%20personal%20identifying%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2247906%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2247924%22%20slang%3D%22en-US%22%3ERE%3A%20Date%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247924%22%20slang%3D%22en-US%22%3EOops.%20Should%20have%20said%20Microsoft%20365.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2247925%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247925%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244483%22%20target%3D%22_blank%22%3E%40cbrigham%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20%22dates%22%20are%20left-aligned%2C%20indicating%20that%20they%20are%20text%20values%20that%20look%20like%20dates.%3C%2FP%3E%0A%3CP%3EI'd%20start%20by%20converting%20them%20to%20real%20dates%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20column%20A.%3C%2FLI%3E%0A%3CLI%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20click%20Text%20to%20Columns.%3C%2FLI%3E%0A%3CLI%3EClick%20Next%20%26gt%3B%20twice.%3C%2FLI%3E%0A%3CLI%3ESelect%20Date%2C%20then%20select%20MDY%20from%20the%20dropdown%20next%20to%20it.%3C%2FLI%3E%0A%3CLI%3EClick%20Finish.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3ENext%2C%20create%20a%20helper%20column%20to%20the%20right%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIn%20B2%20enter%20the%20formula%20%3DTEXT(A2%2C%22mm%2Fdd%22)%3C%2FLI%3E%0A%3CLI%3EFill%20down.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EFinally%2C%20sort%20the%20entire%20range%20(not%20just%20column%20B)%20on%20column%20B.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2247930%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247930%22%20slang%3D%22en-US%22%3EThank%20you.%20I%20will%20try%20this%20solution!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2248183%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2248183%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F21903%22%20target%3D%22_blank%22%3E%40hans%3C%2FA%3E%20Vogelear%3CBR%20%2F%3EWorked%20perfectly!%20Thanks%20again.%3C%2FLINGO-BODY%3E
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.