How to convert an existing dd mmm, yyyy h:mm a/pm format to enable a chronological sort?

Hi suekatsue,

Thank you, I found your document. This is what I did with made-up dates in Column A:

This approach uses the LEFT and MID functions to pull values from a text string in Column A.

Formula in F2 uses the DATE function (Year, Month, Day) to construct a Date format that Numbers will recognise.

To extract Year, Month, Day from the text in column A, fill these formulas down.

Formula in B2 to find the first space =FIND(" ",A2)

Formula in C2 to extract the day of the month =LEFT(A2,B2−1)

To find the month number (required by the DATE function) we need a Lookup table:

Formula in D2 =VLOOKUP(MID(A2,B2+1,3),Lookup::A:B,2)

Formula in E2 =MID(A2,B2+6,4)

Now for the date, formula in F2 =DATE(E2,D2,C2)

I am following SG's script method with interest!

Regards,

Ian.

That extra non-standard , throws Numbers off. A short script can quickly solve problems like this, by removing the , and reordering the date so that Numbers recognizes it. With the region set to US, this script corrects the problem on my machine. It takes only a couple of minutes.

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with c in selRng's cells

set v to c's value

set ww to v's words

set yr to v's word 3

set mo to v's word 2

set da to v's word 1

set ti to v's word 4 & ":" & v's word 5 & " " & v's word 6

set reorderedDate to mo & " " & da & ", " & yr & " " & ti

set c'svalue to reorderedDate

end repeat

end tell

end tell

end tell

1. Copy-paste the script into Script Editor (in the Applications > Utilities folder).
2. Make sure Script Editor.app is listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.
3. Select the cells with the dates that aren't recognized as dates and thus won't sort.
4. Click the 'run' button in Script Editor.

If your region isn't set to US you can easily rearrange the

setreorderedDateto

line to an order will work in your region. Just post explaining what date formats are valid in your region if you need help on that.

Once Numbers recognizes the value as a valid date (which it will right-align in the cell) you can of course go to Data Format in the Cell tab of the panel at the right and change the format to whatever you want (and it will still sort correctly).

SG

P.S. As always, since this changes the values in the cells best to test on a copy of your data first.

It seems as if among the cells you selected before running the script are some ones without dates, at least one with the word Account in it. Make sure you select only cells with the dates in them that you want to correct. Then run the script, and if it doesn't give you the results you want, report back the results here and I'll modify it.

I've tested it on the Numbers document you posted and it successfully converts the dates to true dates. My region (at System Preferences > Language & Region) is set to United States. If yours is different perhaps you could advise what it is.

SG

suekatsue wrote:

I have never used script editor, but I will need to study it further to grasp how to apply it.

There is nothing to study! It's a simple copy-paste of the script into Script Editor. You've already done that step.

Then it's a simple matter of selecting the correct cells in Numbers and clicking the 'run' button in Script Editor. That's it!

In your case the correct cells to select are those containing the dates you want to convert. (Don't select header cells or other cells not containing dates and don't select blank cells). That it!

No extra columns. No formulas. Just select and click 'run'. And you can reuse it next time if you periodically download a csv from the same source, without having to once again set up extra columns with formulas.

SG

Hi Susan,

SG's script works for me. I edited this line to put day month year (my preferred format)

set reorderedDate to da & " " & mo & ", " & yr & " " & ti

My region does not allow formats with a comma after the month, but it is definitely a Date & Time format.

I copied the text dates into Table 1-1 so I could see changes.

Give it a try!

Regards,

Ian.