Date Insanity with Numbers and Applescript

Scripting Forums AppleScript | Mac OS X
#1

I bought iWork to try and see if I can convert Excel Applescripts and I ran into a date anomaly. Here’s a test script to run against iWork/Numbers:


tell application "Numbers"
	activate
	set test to make new document
	tell table 1 of active sheet of test
		set newWidth to (width of column 1 as integer) * 3
		set width of column 1 to newWidth
		set width of column 2 to newWidth

		set value of (cell 1 of row 1) to "10/24/14"
		
		set value of (cell 1 of row 2) to (cell 1 of row 1)'s formatted value as string
		set value of (cell 2 of row 2) to "formatted value as string"
		
		set value of (cell 1 of row 3) to (cell 1 of row 1)'s value as string
		set value of (cell 2 of row 3) to "value as string - why is day wrong?"
		
		set value of (cell 1 of row 4) to (cell 1 of row 1)'s value as date
		set value of (cell 2 of row 4) to "value as date - correct"
	end tell
end tell

Here’s the spreadsheet display (in case you don’t have numbers):

#2

That does look odd. Maybe it’s because of time differences in your location. Maybe there’s a way to set your location in the preferences? I haven’t used Office in a while so don’t know.

Edited: maybe that could be utc time.

gl,
kel

#3

Well, thanks for replying but this is not office - it’s iWork.

I don’t see how time zone is relevant to example code although if you take -5 from 12AM, you do get 7PM the previous day which probably isn’t a coincidence but I still don’t see how that plays into the posted script. It’s getting the same value as formatted string or just string.

#4

Hi Steve,

Yeah, that was just an observation in thinking that maybe there was something in preferences for Numbers. Yvan should be here soon. :slight_smile:

gl,
kel

#5

Here I am.

point 1 :
My system is set to run in French so as it’s late, I made tests with this settings.
I was forced to edit
set value of (cell 1 of row 1) to “24/10/14”
as
set value of (cell 1 of row 1) to “10/24/14”
It may be an important detail because here, I got :
A2 : 24/10/14
A3 : vendredi 24 octobre 2014 02:00:00
A4 : 24/10/2014 00:00:00

point 2 :
coercing (cell 1 of row 1)'s formatted value as string is wasting time because formatted value is defined as a string objet.

point 3 :
Here I’m puzzled by the time value. I would have an explanation if the value was 01:00:00 which is the current offset from UTC (aka GMT) - since last Sunday, we aren’t in daylight time.

I made new test with an edited script :

log (time to GMT)
"1/1/1"
set origDate to date result
set year of origDate to 2014
set month of origDate to 10
set day of origDate to 24
log origdate
set origDate to short date string of origDate

tell application "Numbers"
	activate
	set test to make new document
	tell table 1 of active sheet of document 1 # so event log is easier to read
		set newWidth to (width of column 1 as integer) * 3
		set width of column 1 to newWidth
		set width of column 2 to newWidth
		
		set value of (cell 1 of row 1) to origDate

		set maybe to (cell 1 of row 1)'s formatted value
		log maybe
		set value of (cell 1 of row 2) to maybe
		set value of (cell 2 of row 2) to "formatted value which is a string"

		set maybe to (cell 1 of row 1)'s value as string
		log maybe
		set value of (cell 1 of row 3) to maybe
		set value of (cell 2 of row 3) to "value as string - why is day wrong?"

		set maybe to (cell 1 of row 1)'s value as date
		log maybe
		set value of (cell 1 of row 4) to maybe
		set value of (cell 2 of row 4) to "value as date - correct"

		set maybe to (cell 1 of row 3)'s value as date
	end tell
end tell

The events log was :

tell application "Script Editor"
	time to GMT
end tell
(*3600*)
(*date vendredi 24 octobre 2014 00:00:00*)
tell application "Numbers"
	activate
	make new document
	get width of column 1 of table 1 of active sheet of document 1
	set width of column 1 of table 1 of active sheet of document 1 to 216
	set width of column 2 of table 1 of active sheet of document 1 to 216
	set value of cell 1 of row 1 of table 1 of active sheet of document 1 to "24/10/2014"
	get formatted value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*24/10/2014*)
	set value of cell 1 of row 2 of table 1 of active sheet of document 1 to "24/10/2014"
	set value of cell 2 of row 2 of table 1 of active sheet of document 1 to "formatted value as string"
	get value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*vendredi 24 octobre 2014 02:00:00*)
	set value of cell 1 of row 3 of table 1 of active sheet of document 1 to "vendredi 24 octobre 2014 02:00:00"
	set value of cell 2 of row 3 of table 1 of active sheet of document 1 to "value as string - why is day wrong?"
	get value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*date vendredi 24 octobre 2014 02:00:00*)
	set value of cell 1 of row 4 of table 1 of active sheet of document 1 to date "vendredi 24 octobre 2014 02:00:00"
	set value of cell 2 of row 4 of table 1 of active sheet of document 1 to "value as date - correct"
end tell
--> date "vendredi 24 octobre 2014 04:00:00"

As you may see, extracting formatted value returns a string without any explicit coercion
But the cell 1 of row 2 contains in fact an automatically formatted object which is a date displayed without time

We may see that coercing the value of cell 1 of row 1 into a string doesn’t give the original AppleScript date which was logged on entry as (date vendredi 24 octobre 2014 00:00:00)
We have a two hours offset which is an odd one as we are at winter time.

We may also see that coercing explicitly the value of cell 1 of row 1 into a string gives also the extraneous wrong offset.

My understanding is :
1 - that Numbers wrongly treat the UTC value. I’m afraid that the date used to switch to winter time is the US one
2 - I wouldn’t be surprised by the fact that the Original Poster’s location is one with a huge time to GMT offset which introduce the wrong day value.

I am curious to see what he will get after running my version of the script.
I highlighted the word script to be sure that he don’t try to run the event log :wink:

Yvan KOENIG (VALLAURIS, France) mardi 4 novembre 2014 23:20:09

#6

Glad to see you! :smiley:

#7

With iWork '09, we had to coerce date values to string before using them to set the value of a cell.
With iPlay '13 we can’t have a correct behavior.
Just to demo that, here is a slight variation of the script posted yesterdays.

log (time to GMT)
"1/1/1"
set origDate to date result
set year of origDate to 2014
set month of origDate to 10
set day of origDate to 24
log origDate
--set origDate to short date string of origDate # DISABLED  so that we set the initial value to a date

tell application "Numbers"
	activate
	set test to make new document
	tell table 1 of active sheet of document 1 # so event log is easier to read
		set newWidth to (width of column 1 as integer) * 3
		set width of column 1 to newWidth
		set width of column 2 to newWidth
		
		set value of (cell 1 of row 1) to origDate
		
		set maybe to (cell 1 of row 1)'s formatted value
		log maybe
		set value of (cell 1 of row 2) to maybe
		set value of (cell 2 of row 2) to "formatted value which is a string"
		
		set maybe to (cell 1 of row 1)'s value as string
		log maybe
		set value of (cell 1 of row 3) to maybe
		set value of (cell 2 of row 3) to "value as string - why is day wrong?"
		
		set maybe to (cell 1 of row 1)'s value as date
		log maybe
		set value of (cell 1 of row 4) to maybe
		set value of (cell 2 of row 4) to "value as date - correct"
		
		set maybe to (cell 1 of row 3)'s value as date
	end tell
end tell

This time , the events log is :

tell application "Script Editor"
	time to GMT
end tell
(*3600*)
(*date vendredi 24 octobre 2014 00:00:00*)
tell application "Numbers"
	activate
	make new document
	get width of column 1 of table 1 of active sheet of document 1
	set width of column 1 of table 1 of active sheet of document 1 to 216
	set width of column 2 of table 1 of active sheet of document 1 to 216
	set value of cell 1 of row 1 of table 1 of active sheet of document 1 to date "vendredi 24 octobre 2014 00:00:00"
	get formatted value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*23/10/2014 22:00*)
	set value of cell 1 of row 2 of table 1 of active sheet of document 1 to "23/10/2014 22:00"
	set value of cell 2 of row 2 of table 1 of active sheet of document 1 to "formatted value which is a string"
	get value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*vendredi 24 octobre 2014 00:00:00*)
	set value of cell 1 of row 3 of table 1 of active sheet of document 1 to "vendredi 24 octobre 2014 00:00:00"
	set value of cell 2 of row 3 of table 1 of active sheet of document 1 to "value as string - why is day wrong?"
	get value of cell 1 of row 1 of table 1 of active sheet of document 1
	(*date vendredi 24 octobre 2014 00:00:00*)
	set value of cell 1 of row 4 of table 1 of active sheet of document 1 to date "vendredi 24 octobre 2014 00:00:00"
	set value of cell 2 of row 4 of table 1 of active sheet of document 1 to "value as date - correct"
	get value of cell 1 of row 3 of table 1 of active sheet of document 1
end tell
Résultat :
date "vendredi 24 octobre 2014 02:00:00"

As you see, when we insert a date value, the application substract it the wrong offset to UTC so, we move to the preceding day.

I will have to file a bug report but before doing that I wish to get what the two scripts return on the Original poster machine and for instance on kel’s one.

I had a look in Wikipedia and found :
Start and end dates vary with location and year. Since 1996 European Summer Time has been observed from the last Sunday in March to the last Sunday in October; previously the rules were not uniform across the European Union.[39] Starting in 2007, most of the United States and Canada observe DST from the second Sunday in March to the first Sunday in November, almost two-thirds of the year.

So the wrong value of the offset to UTC is not linked to a wrong setting of the change to winter time.
Here in France we made it on Sunday 2014/10/26 and USA made it on Sunday 2014/11/2 so we are all supposed to be in winter time.

Yvan KOENIG (VALLAURIS, France) mercredi 5 novembre 2014 10:05:44

#8

Hi Yvan.

I can’t comment on the current version of Numbers. But with Numbers '09, a date string entered into a cell does seem to be interpreted as UTC and the resulting cell value is a date which is the local equivalent of the UTC one. I’d regard this as a bug.

24th October was before the clocks went back, so here in the UK, entering the text “24 October 2014 00:00:00” results in a cell value of date “24 October 2014 01:00:00” ” because we were on BST then, which is one hour ahead of GMT (UTC).

time to GMT returns the GMT offset at the time the script’s run, so using it now returns an offset which is one hour different from what it would be on 24th October. If you change the test date in your script to 24 November, the offset from GMT is exactly the same.

Another point is that there’s still a bug in AppleScript where the string representations of very early dates are a few seconds out from the values of the dates’ properties. The amount of the difference apparently increases the further west you are. For me, in the UK, the difference is 75 seconds:

"1/1/1"
set origDate to date result
--> date "Monday 1 January 1 00:00:00"

-- But:
{weekday, day, month, year, hours, minutes, seconds} of origDate
-- {Monday, 1, January, 1, 0, 1, 15}

Building the date 24th October 2014 from this takes it into an era where the bug doesn’t occur, so coercing the result back to text produces “Friday 24 October 2014 00:01:15”. And of course entering this into Numbers gives a cell value of date “Friday 24 October 2014 01:01:15”. Being much further west, Steve and Kel will get even weirder results. You appear to be in a time zone where the AppleScript bug doesn’t make any difference.

#9

FWIW, I’m seeing the same thing with this script and the latest version (and I’m on GMT+1100 since early last month). I’d regard this as a serious bug.

#10

I think that should probably be the other way round: “. a date string entered into a cell does seem to be interpreted as UTC and the resulting cell value is a date which is the local equivalent of the UTC one.” I’ve corrected it above.

#11

Thanks Nigel.

I’m really an ass failing to see that 2014/10/24 was in summer time.
What remain meaningful with my two versions of the script is that we may get odd results if we insert dates coerced into strings with the late Numbers (v 3.x)
Just for check, run :

log (time to GMT)
"1/1/1"
set origDate to date result
set year of origDate to 2014
set month of origDate to 11
set day of origDate to 5
log origDate
set origDate to short date string of origDate

tell application "Numbers"
	activate
	set test to make new document
	tell table 1 of active sheet of document 1 # so event log is easier to read
		set newWidth to (width of column 1 as integer) * 3
		set width of columns 1 thru 3 to newWidth
		
		set value of cells 1 thru 3 of row 1 to origDate
		
		repeat with r from 1 to 10
			set maybe to (cell 1 of row r)'s value as date
			log maybe
			set value of cell 1 of row (r + 1) to maybe
			
			set maybe to (cell 2 of row r)'s value as string
			log maybe
			set value of cell 2 of row (r + 1) to maybe
			
			set maybe to (cell 3 of row r)'s formatted value
			log maybe
			set value of cell 3 of row (r + 1) to maybe
		end repeat
		
		set maybe to (cell 1 of row (r + 1))'s value as date
		log maybe
		set maybe to (cell 2 of row (r + 1))'s value as date
		log maybe
		set maybe to (cell 3 of row (r + 1))'s value as date
	end tell
end tell

and alt version

log (time to GMT)
"1/1/1"
set origDate to date result
set year of origDate to 2014
set month of origDate to 11
set day of origDate to 5
log origDate
--set origDate to short date string of origDate

tell application "Numbers"
	activate
	set test to make new document
	tell table 1 of active sheet of document 1 # so event log is easier to read
		set newWidth to (width of column 1 as integer) * 3
		set width of columns 1 thru 3 to newWidth
		
		set value of cells 1 thru 3 of row 1 to origDate
		
		repeat with r from 1 to 10
			set maybe to (cell 1 of row r)'s value as date
			log maybe
			set value of cell 1 of row (r + 1) to maybe
			
			set maybe to (cell 2 of row r)'s value as string
			log maybe
			set value of cell 2 of row (r + 1) to maybe
			
			set maybe to (cell 3 of row r)'s formatted value
			log maybe
			set value of cell 3 of row (r + 1) to maybe
		end repeat
		
		set maybe to (cell 1 of row (r + 1))'s value as date
		log maybe
		set maybe to (cell 2 of row (r + 1))'s value as date
		log maybe
		set maybe to (cell 3 of row (r + 1))'s value as date
	end tell
end tell

Yvan KOENIG (VALLAURIS, France) mercredi 5 novembre 2014 12:56:41

#12

Yvan, taking your alt version and changing the loop count to 20 or 30 shows an increasing data delta. I still can’t determine where it’s coming from and thus don’t know how to work around.

I need to be able to read external csv files from excel with string dates like “11/5/14” then use applescript to loop down the rows to see if a date matches (m/d/y). So, I have to coerce both to dates or both to strings for equality test and that’s when I found the anomaly.

#13

The explanation of the offset is simple.
Each time we set the value of a cell to a date-string, it’s given the offset to UTC component.
so here,
2014/11/05 becomes 2014/11/05 01:00:00
then
2014/11/05 01:00:00 becomes 2014/11/05 02:00:00
2014/11/05 02:00:00 becomes 2014/11/05 03:00:00
.

Which is the value of (time to GMT) for you and which is the offset which you get in column 2 ?

Here, for some months, time to GMT is 3600 (winter time) which is 60*60 aka one hour.
My own tip to get rid of that is to use an (or several) auxiliary column(s).
If I use a script to store date-strings in cells of a column ” say column 2,
I insert the formula =“”&b1 in cell C1 and fill down the column.
This way the conversion from a date value to a real string is done internally by Numbers without AppleScript and the behavior is OK.

Here is a short script (apply it to an existing blank document) demonstrating two ways to achieve the same goal.

if (do shell script "defaults read 'Apple Global Domain' AppleLocale") does not start with "fr_" then
	# Date-text values using the format mm/dd/yyyy
	set theDates to {"05/12/2014", "06/13/2014 01:00", "07/14/2014 02:00"}
else
	# Date-text values using the format dd/mm/yyyy
	set theDates to {"12/05/2014", "13/06/2014 01:00", "14/07/2014 02:00"}
end if

# Extract the localized names of two functions used below
set DATEVALUE_loc to localized string "DATEVALUE" from table "TSCalculationEngine" in bundle path to application "Numbers"
# in French --> DATEVAL
set TIMEVALUE_loc to localized string "TIMEVALUE" from table "TSCalculationEngine" in bundle path to application "Numbers"
# in French --> TEMPSVAL

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	repeat with i from 1 to 3
		set maybe to (item i of theDates)
		# Inserts a date-text value in the cell. It will be deciphered automatically as a date
		set value of cell 1 of row (i + 1) to maybe
		# Inserts a formula which will convert the date in a true text object
		set value of cell 2 of row (i + 1) to "=" & quote & quote & "&" & (name of cell 1 of row (i + 1))
		# Inserts a date-text value in the cell. It will be deciphered automatically as a date
		set value of cell 3 of row (i + 1) to maybe
		# Apply the format text to the date
		set format of range (name of cell 3 of row (i + 1)) to text
		# Insert a formula which convert the true date-text into a true date
		set value of cell 4 of row (i + 1) to "=" & DATEVALUE_loc & "(" & name of cell 3 of row (i + 1) & ")+" & TIMEVALUE_loc & "(" & name of cell 3 of row (i + 1) & ")"
	end repeat
end tell

I know that the code isn’t optimized. It’s just a draft demoing the way to achieve goals.

Yvan KOENIG (VALLAURIS, France) mercredi 5 novembre 2014 17:08:40

#14

Thank you so much! Your effort is appreciated. The latest script works and I’ll analyze it to make sure I understand.

My GMT offset is -5.

The issue is non-intuitive. It seems if a user paste a date in sans time, it should be 00:00:00 (12AM) and I’m pretty sure that’s what the documentation states.

#15

The problem with the late iPlay '13 is that there is no longer user guides in PDFs.
We must navigate in helps organized as Web sites.
I don’t know if English users are able to find easily what they need with this system.
For a french users it’s really difficult because the keywords are translated from English ones and most of the time, I am unable to find an info.
At this time, I keep carefully the PDFs delivered with iWork '09.
Honestly, I’m not sure that the guys which wrote the Guides (or the Help) really use the applications and really know how they behave.
On the other hand, I’m sure of one point : the User guides (and probably the help) describe the behavior of the applications, not the way they interact with AppleScript.

If I type by hand 11/11/2014 in cell B11 whose format is the default “automatic” one, the cell contain the typed date with the default time value : 00:00:00.
We may check that applying a format displaying the time component.
We may also type in C11 the formula =TIMEVALUE(B11) which will give 0.

Things become “surprising” when AppleScript is used.
Just an example here :

set date1 to current date
set date2 to (current date) as text
tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	set value of cell "B12" to date1
	set value of cell "B13" to date2
	value of cell "B11"
end tell

Here in France, it returns :
date “samedi 11 novembre 2014 01:00:00”
I guess that on your machine, it will return monday november 10, 2014 22:00:00

It’s why I wrote the script posted as message #13.
With it, I hope that every user would be able to understand the way Numbers an AppleScript interact.

When I am more lazy than I am currently, I don’t use a script to import datas borrowed from a CSV file, I dag and drop the file on the Numbers icon. This way Numbers is the unique tool deciphering the datas and the result is “normal”.

Yvan KOENIG (VALLAURIS, France) mercredi 5 novembre 2014 21:08:10

#16

Hello

I tried to write a summary of the messages posted before.
Here it is :

What we must know to work with dates in Numbers.

(1) When we type a date value without a time component, the application complete it with the time value 0 which means 00:00:00.
(2) When we paste a date-text without a time component, the application complete it with the time value 0 which means 00:00:00.
(3) If we use GUIScripting to mimic the actions described above, we get the same results.

(4) If we use AppleScript to set the value of a cell to a date-text value the cell’s content will be a date.
If we set its format to display the time component we will see that this one matches the one available in the string (no time in the string will become 00:00:00).
Here in France, setting the value to the date-text 12/12/1943 will insert in fact 11/12/1943 00:00:00.
Here in France, setting the value to the date-text 8/8/2014 will insert in fact 8/8/2014 00:00:00.
(5) If we use AppleScript to extract the value from the cell filled with a date-text value (case 4), we will get a date value equal to the date plus the (time to GMT) value linked to the source date in the location where we do the job.
Here in France, the cell which was filled with “12/12/1943” will give 12/12/1943 01:00:00 (winter time).
Here in France, the cell which was filled with “08/08/2014” will give 08/08/2014 02:00:00 (summer time time).

(6)If we use AppleScript to set the value of a cell to a date value the cell’s content will be the date minus the (time to GMT) linked to the source date in the location where we do the job.
Here in France, setting the value to the date 12/12/1943 will insert in fact 11/12/1943 23:00:00 because the offset is one hour (winter time)
Here in France, setting the value to the date 8/8/2014 will insert in fact 7/8/2014 22:00:00 because the offset is two hours (summer time)
(7) If we use AppleScript to extract the value from the cell filled with a date value (case 6), we will get a date value equal to the original date which means, the stored date plus the (time to GMT) value linked to the date in the location where we do the job.
Here in France, the cell which was filled with date “12/12/1943” (it display 11/12/1943 23:00:00) will give 12/12/1943 00:00:00.
Here in France, the cell which was filled with date “08/08/2014” (it display 07/08/2014 22:00:00) will give 08/08/2014 00:00:00.

(8) If we need to be able to extract dates from a table using AppleScript, the safe way is to store the dates as text objects.
To do that, set value of the cell to the date value then set the format of the range (name of the cell) to text.
Of course, the table will be unable to make calculations upon these dates stored as text.
To be able to do that we will have to use an auxiliary column in which date-text values will be converted into date-time objects.
This may be achieved with this kind of code :

# Extract the localized names of two functions used below
set DATEVALUE_loc to localized string "DATEVALUE" from table "TSCalculationEngine" in bundle path to application "Numbers"
# in French --> DATEVAL
set TIMEVALUE_loc to localized string "TIMEVALUE" from table "TSCalculationEngine" in bundle path to application "Numbers"
# in French --> TEMPSVAL

set theDates to {"8/8/2014", "12/12/1943"}

tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
	repeat with i from 1 to 2
		set maybe to (item i of theDates)
		(*
		# Inserts a date-text value in the cell. It will be deciphered automatically as a date
		set value of cell 1 of row (i + 1) to maybe
		# Inserts a formula which will convert the date in a true text object
		set value of cell 2 of row (i + 1) to "=" & quote & quote & "&" & (name of cell 1 of row (i + 1))
		*)
		# Inserts a date-text value in the cell. It will be deciphered automatically as a date
		set value of cell 3 of row (i + 1) to maybe
		# Apply the format text to the date
		set format of range (name of cell 3 of row (i + 1)) to text
		# Insert a formula which convert the true date-text into a true date
		set value of cell 4 of row (i + 1) to "=" & DATEVALUE_loc & "(" & name of cell 3 of row (i + 1) & ")+" & TIMEVALUE_loc & "(" & name of cell 3 of row (i + 1) & ")"
	end repeat
end tell

I disabled the alternate scheme in which we leave the cells filled by the script in automatic date format and insert a formula converting the date into a true text object.
If we want to extract the date with a script, we will have to do that from the cells containing the formula converting date to text.

I know, it’s boring, but we can’t change the application behavior so the best thing to do is to learn how we may speak to it to achieve our goals.

Yvan KOENIG (VALLAURIS, France) jeudi 6 novembre 2014 10:55:18