Question on Date formatting

Do all the "dates" show up as right-aligned in the cells? If not Numbers may not be recognizing some of them as dates.

It might help if you post a screenshot. On the Mac you can post a screenshot this way:

1. shift-command-4

2. holding mouse down drag crosshairs across the area you want to capture, and release

3. a file named something like Screen Shot 2014-01-29 at 7.27.28 AM.png will be placed on desktop

4. in editor here click camera icon, 'Choose File', navigate to the file on the Desktop and click, click 'Choose', click 'Insert Image'

Sometimes the "camera" doesn't work the first time, so you just redo the steps in 4.

SG

Unless DD/MM/YY is one of the format choices in your country-version, that date will not be recognized. Same goes for YYYY-MM-DD. Are those two choices among the options in the Date format menu on your system?

Jerry

If YYYY-MM-DD is one of the recognized date formats and shows up in your Numbers menu:

Then you could try this script as a quick way to convert the "dates" shown in your screenshot to that format. Copy and paste into AppleScript Editor, select the cells in your spreadsheet with the "dates", and click the green triangle "Run" button in AppleScript Editor. Test it on copies of the data first, though, to make sure it is doing the right thing for your setup!

SG

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

end repeat

end tell

--end script

Hi SG,

Are your scripted dates really dates? I am finding some odd results with the Numbers way of concatenating.

B2 =2014&"-"&11&"-"&7

It is left aligned and shows Automatic format. Setting it to Date and Time does not stick. Yet if I use that cell in a formula, it works as a date.

C2 =B2+1

D2 =B2+"1d"

Both add a day, and will keep Date and Time format.

BTW, my region allows YYYY-MM-DD.

Regards,

Ian.

Hi Ian,

Are your scripted dates really dates?

No, not really dates. I should have made that clearer. My idea is that the orginal cells, since they were left-aligned and probably not in a format Numbers can recognize as anything but a "plain string", might be formatted as Text, not as Date & Time. So the script would be a quick way of taking those non-date strings and converting them to YYYY-MM-DD format, after which one could apply Date & Time format via the format panel on the right.

The last step, the manual formatting as Date & Time, can be scripted by adding the following line before the

end repeat :

set format to date and time

That statement applies to the entire selection range. Will be interested to here if that works on your machine.

SG

Hi SG,

SG wrote:

Will be interested to here if that works on your machine.

OK,

adding the following line before the

end repeat :

set format to date and time

Script is now:

--converts string from dd/mm/yyyy to yyyy-mm-dd

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 1 thru 2 of aValue

set myMonth to text 4 thru 5 of aValue

set myYear to text 7 thru 10 of aValue

set value of aCell to myYear & "-" & myMonth & "-" & myDay

set format to date and time

end repeat

end tell

--end script

Copied and pasted into AppleScript Editor. Selected DD/MM/YYY values in Numbers (a saved document)

Clicked on Run in AppleScript Editor.

As you can tell, I am new to scripting.

Regards,

Ian.

Hi Ian,

Maybe it's because you have Numbers 2.3 open at the same time?

Or another possibility is that the copy-paste of the script introduced some invisible gremlins. You could try removing the indents so all lines are flush left and then recompile with the "hammer."

Note that ...

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

....is one line.

SG

Hi SG,

Maybe it's because you have Numbers 2.3 open at the same time?

Yes, I did (cringe) but still does not work with only Numbers 3.2 running.

Screen shot of the script in Pages '09 (Pages 4.3) with Menu > View > Show Invisibles

There could be some gremlins in the script I copied from your post (and then added the set format command). The line of blue dots represents ordinary spaces. The lines of gremlins that look like colons (but aren't) could be non-breaking spaces?

Anyway, we are getting off topic. I shall start a new discussion.

https://discussions.apple.com/thread/6068617?tstart=0

Regards,

Ian.

Hello

Did you import the data from csv file? I suspect that each row except for the last ends with CR which keeps your data from being interpreted as date string. The CR will be the residue of CRLF cluster in the original csv file which has been improperly imported by using LF as line terminator.

If my suspect is correct, you'd need to clean up your data.

Regards,

H

Hello

There're several characters used to indicate line termination in text, which include -

U+000A LINE FEED (LF);

U+000D CARRIAGE RETURN (CR);

U+2029 PARAGRAPH SEPARATOR;

and the combination of CR + LF, which is used in internet messaging as well as in common csv.

E.g. Given csv text -

27/11/1998 CR LF
25/11/1998 CR LF
23/11/1998 CR LF
21/11/1998

if you import it using LF as line terminator for some reason, it will result in rows -

27/11/1998 CR
25/11/1998 CR
23/11/1998 CR
21/11/1998

and those except for the last contain unwanted trailing CR, which will prevent the data from being interpreted as date string.

This is what I suspect and if it is correct, the remedy is to remove unwanted CRs.

Regards,

H

Hi H,

I tried to test that in Numbers 3 with this:

set dateStr1 to "27/11/1998" & return & linefeed & "25/11/1998" & return & linefeed & "23/11/1998"

set dateStr2 to "1998-11-27" & return & linefeed & "1998-11-25" & return & linefeed & "1998-11-23"

set dateStr3 to "1998-11-27" & return & "1998-11-25" & return & "1998-11-23"

set the clipboard todateStr3--dateStr1 or dateStr2

All three strings paste into Numbers 3 as a column of three clls. But on my machine with my localization dateStr1is not recogized as dates, whereas dateStr2 and dateStr3 can be formatted as dates.

So I'm guessing that in Numbers 3 the date format ("27/11/1998" vs "1998-11-27") is the major issue. I'm not sure how to get Numbers to recognize "27/11/1998" but suspect it might be related to localization settings.

SG