SOLVED

Unable to sort a column of dates in Excel for Mac 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-2140281%22%20slang%3D%22en-US%22%3EUnable%20to%20sort%20a%20column%20of%20dates%20in%20Excel%20for%20Mac%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2140281%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20master%20excel%20spreadsheet%20in%20Excel%20for%20Mac%202016%20I%20have%20rows%20of%20data%20each%20row%20with%20a%20date%3C%2FP%3E%3CP%3EIn%20the%20attached%20excel%20file%20I%20extracted%20the%20column%20of%20dates%20I%20need%20to%20sort%20by%20date%20earliest%20to%20latest%20and%20put%20that%20column%20in%20a%20new%20excel%20sheet%20so%20I%20can%20practice.%3C%2FP%3E%3CP%3EI%20am%20unable%20to%20figure%20out%20how%20to%20do%20it.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3EJim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2140281%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2140317%22%20slang%3D%22en-US%22%3ERe%3A%20Unable%20to%20sort%20a%20column%20of%20dates%20in%20Excel%20for%20Mac%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2140317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F968630%22%20target%3D%22_blank%22%3E%40Jim_Cross%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EColumn%20A%20is%20formatted%20as%20Text%2C%20so%20Excel%20sees%20he%20values%20in%20column%20A%20as%20text%2C%20no%20as%20date.%3C%2FP%3E%0A%3CP%3EDo%20the%20following%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20A3%3AA44.%3C%2FLI%3E%0A%3CLI%3ESet%20the%20number%20format%20to%20Short%20Date.%3C%2FLI%3E%0A%3CLI%3EUse%20the%20Replace%20dialog%20to%20change%20June%20to%20Jun%20and%20July%20to%20Jul.%3C%2FLI%3E%0A%3CLI%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20click%20Text%20to%20Columns.%3C%2FLI%3E%0A%3CLI%3EIn%20Step%201%2C%20celect%20Delimited%20and%20click%20Next%20%26gt%3B.%3C%2FLI%3E%0A%3CLI%3EIn%20Step%202%2C%20clear%20all%20Delimiters%20check%20boxes%20and%20click%20Next%20%26gt%3B.%3C%2FLI%3E%0A%3CLI%3EIn%20Step%203%2C%20select%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%3C%2FLINGO-BODY%3E
New Contributor

In my master excel spreadsheet in Excel for Mac 2016 I have rows of data each row with a date

In the attached excel file I extracted the column of dates I need to sort by date earliest to latest and put that column in a new excel sheet so I can practice.

I am unable to figure out how to do it.

Any help would be appreciated.

Jim

 

5 Replies

@Jim_Cross 

Column A is formatted as Text, so Excel sees he values in column A as text, no as date.

Do the following:

  • Select A3:A44.
  • Set the number format to Short Date.
  • Use the Replace dialog to change June to Jun and July to Jul.
  • On the Data tab of the ribbon, click Text to Columns.
  • In Step 1, celect Delimited and click Next >.
  • In Step 2, clear all Delimiters check boxes and click Next >.
  • In Step 3, select Date, then select MDY from the dropdown next to it.
  • Click Finish.

@Hans Vogelaar 

 

Hans

I don't have some of the options you suggest on Office for Mac 2016.

Are you thinking of regular Microsoft Office?

Jim

@Jim_Cross 

I have Office for Windows, so I can't help you with specific details of Excel on MacOS.

Best Response confirmed by Jim_Cross (New Contributor)
Solution

@Jim_Cross

Hi

Insert a column and try using this formula,=DATEVALUE(MID(A3,5,2)&"/"&LEFT(A3,3)&"/"&RIGHT(A3,4))
Then copy and paste special so your just left with the date values, then sort using Oldest to Newest or visa versa.

 

Example attached

 

@Rich99 

 

Many thanks

Worked fine

Jim