In my master excel spreadsheet in Excel for Mac 2016 I have rows of data each row with a dateIn 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

Jim_CrossHiInsert 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

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

5 Replies

Rich99
Iron Contributor
Feb 18, 2021
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

date sort.xlsx10 KB
- Jim_Cross
  Copper Contributor
  Feb 19, 2021
  Rich99
  
  Many thanks
  Worked fine
  Jim
HansVogelaar
MVP
Feb 16, 2021
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.
- Jim_Cross
  Copper Contributor
  Feb 18, 2021
  HansVogelaar
  
  Hans
  I don't have some of the options you suggest on Office for Mac 2016.
  Are you thinking of regular Microsoft Office?
  Jim
  - HansVogelaar
    MVP
    Feb 18, 2021
    Jim_Cross
    
    I have Office for Windows, so I can't help you with specific details of Excel on MacOS.

Forum Discussion

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

5 Replies

Resources