SOLVED

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

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