https://www.icloud.com/numbers/0uXuEtZBnceKUYPpUbysZnk4Q#Star_-_CB_by_LV,_by_LBu y
The date/time format of this file sorts the LV Date and Last Buy Date in "alphabetical" and not chronological order.
This date/time format is not an option/doesn't appear in Numbers' date/time format options. I wish to learn how to convert this non-standard format so I can sort by date.
idisk-OTHER, Mac OS X (10.7.4)
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.
Well this is odd. I first opened it in iCloud Numbers and there is a tab of the right that handles data. This is not in desktop numbers. However, I cannot switch the format for LV Date to Date and Time. It can only be on text or Automatic. I am not sure if there is a fix for this, but I would definitely submit a ticket @ bugreport.apple.com; it would be a good contribution.
suekatsue wrote:
add a short script. I have yet to do this, but it is always great to learn a new how-to.
Curious why you haven't tried it. There's really almost nothing to learn. All you have to do is launch Script Editor, copy-paste the script, select cells, and click the 'run' button. All it that takes about a minute or so to know whether it does the job for your setup.
And if it doesn't give the results you need in your setup (it works on my machine with region set to US), just let know what region your machine is set to.
My guess is that this will be MUCH quicker than explaining to Apple and trying to have them adapt Numbers to handle a non-standard date format.🙂
SG
Hi Sue,
I can not access your iCloud link, so I am guessing. Please try SG's script.
As another approach, I had a play with formulas to convert text such as dd mmm, yyyy h:mm a/pm to a Date & Time format that Numbers will recognise.
Some success, but one problem I came across was hours with two digits (10, 11, 12). Are the hours really single digits or are they 01, 02 (two digits)? If consistently two digits, the problem is solvable with formulas.
A question: are you trying to extract only the Date part, or is the Time part also important?
Regards,
Ian.
suekatsue wrote:
It sounds like I just need to take the time to understand and execute.
Ok, good luck. Just keep in mind that posting a reply here in this forum takes more computer savvy and time than just selecting cells with dates and clicking a 'run' button. There really is nothing to "study" here, unless you want to learn how to write a script rather than just use an existing one. Using scripts can be most useful in saving time during work crunches to solve problems like this.
SG
How to convert an existing dd mmm, yyyy h:mm a/pm format to enable a chronological sort?
