Two questions regarding date handling

Okay, as you may know, the Windows version of Excel stores dates as sequential serial numbers so they can be used in calculations. Each date is by default assigned a number according to how many days (in GMT) that has passed since January 1, 1900. (That is, January 1, 1900 is serial number 1 and January 1, 2008 is serial number 39448 because it (according to Excel) is 39,448 days after January 1, 1900.)

I need Applescript to store a date variable like that, and I made a code as follows:

set startdate to current date -- just to have a date to alter
set day of startdate to 1
set month of startdate to 1
set year of startdate to 1900
set time of startdate to 0

set datenumber to ((date "jan 1, 2008") - (time to GMT)) - startdate
set datenumber to (datenumber / (days) as integer) + 1

The reason I create a startDate variable that I change to January 1, 1900 instead of simply writing date “jan 1, 1900” is that the code should be language independent. (I’m not sure if “Jan” stands for January in languages.) I don’t need to do the same for the January 2008 date as it won’t be part of the actual code. (If anyone knows of a simpler way to do this (like if you could write “date 01/01, 1900” or something"), just let me know.) The “+ 1” at the end is to make sure January 1, 1900 is considered day 1 rather than day 0. Anyway, the problem is that this code generates 39947 rather than 39948 so one day is lost somewhere. However, when I adjust the code for the current date:

set startdate to current date -- just to have a date to alter
set day of startdate to 1
set month of startdate to 1
set year of startdate to 1900
set time of startdate to 0

set datenumber to ((current date) - (time to GMT)) - startdate
set datenumber to (datenumber / (days) as integer) + 1

I get 38638 which is the same value a PC version (based on the Excel date) of the program I’m writing gives for today.

Can anyone understand this discrepancy? Is the supposed Excel date value for January 1, 2008 (I got it from here) simply incorrect and the actual value IS 39447?

Or does anyone know of a better way to display date in Windows Excel format?

Any help appreciated.

39447 is correct:

((date "Tuesday 1 January 2008 00:00:00") - (date "Monday 1 January 1900 00:00:00")) div days + 1
--> 39447

Maybe Microsoft’s programmers don’t know that 1900 wasn’t a leap year. :wink:

If you compile a date into a script, as above, in your own (system) language, it’ll work on any Mac. If someone using a French system (say) opens your script in their Script Editor, they’ll see the date in French. However, something like this wouldn’t work for them:

set testDate to "Tuesday 1 January 2008 00:00:00"
((date testDate) - (date "Monday 1 January 1900 00:00:00")) div days + 1

… because ‘date testDate’ would be evaluated at run time according to the local Date and Time preferences and wouldn’t make sense to the local machine.

You don’t need ‘time to GMT’ when subtracting two dates, only when comparing dates that might be in different time zones.

Thanks for the reply. Now there is some language dependency because I can’t compile either of those scripts you posted. (I’m on a Swedish system and “January” has to be changed into “Januari” to be used like that.) Also, I have time to GMT to make sure the result is the same regardless of the timezone in which the code is run.

Anyway, the strange thing I noticed now is that the second script in my post:

set startdate to current date -- just to have a date to alter
set day of startdate to 1
set month of startdate to 1
set year of startdate to 1900
set time of startdate to 0

set datenumber to ((current date) - (time to GMT)) - startdate
set datenumber to (datenumber / (days) as integer) + 1

Still generates 38638 despite current date now being Oct, 14 instead of Oct, 13. What the heck is up with this?

Edit: Maybe I have to make sure the number is always rounded up?

Edit 2: Playing around with the code a bit it appears that this is indeed the case. Is there a function in applescript to round a real number towards the nearest higher integer or do I have to write one myself? (It’s not like it’d be hard, I’m just wondering if there’s an official way to do it.)

The Joker,

Here’s the basic stuff for rounding numbers.

set x to 37.523
set y to 5
set z to round (x / y) rounding down

or

set x to 37.523
set y to 5
set z to round (x / y) rounding up

or

set x to 37.523
set y to 5
set z to round (x / y) rounding as taught in school

or if you just say

set x to 37.523
set y to 5
set z to round (x / y)

it seems to round up. I saw these somewhere and can’t find them again, but I think there might be one or two more expressions after the rounding part of the statement.

PreTech

Yes. Sorry. I wasn’t clear. In the scripts I posted, you have to write the dates in the same language that’s used on the machine used to compile them. But once they’re compiled, they’ll work anywhere. Your method is only necessary when you want to distribute the scripts as source code. (If you want your method to work on systems earlier than 10.4, set the month of startdate to the AppleScript constant ‘January’ rather than to 1.):

set month of startdate to January

‘datenumber / days’ returns a real, which you then coerce to integer. The ability to coerce reals to integer was introduced in OS 10.3 and the effect is to round the real to the nearest whole number. If you ran your script yesterday afternoon (GMT), the result would have been rounded up. When you ran it this morning, it was rounded down, giving the same number of days. The best approach is to use the ‘div’ operator (which jettisons the fractional part of its result ” ie. rounds down), rather than ‘/’.

set datenumber to ((current date) - (time to GMT)) - startdate
set datenumber to (datenumber div days) + 1

‘round (x / y)’ is the same as ‘round (x / y) rounding to nearest’. It rounds down if (x / y)'s fractional part is less than .5 and rounds up if it’s greater than .5. If the factional part is exactly .5, the number is rounded to the nearest even whole number:

round 5.5
--> 6

round 4.5
--> 4

The ‘rounding as taught in school’ parameter has a similar effect, except that numbers whose fractional part is exactly .5 are rounded away from zero:

round 5.5 rounding as taught in school
--> 6

round 4.5 rounding as taught in school
--> 5

round -4.5 rounding as taught in school
--> -5

If you prefer the ‘away from zero’ convention, and don’t care for the patronising terminology required for the ‘round’ command, you can get a much faster result with this handler:

on rnd(n)
	return n div 0.5 - n div 1
end rnd

rnd(-4.5)
--> -4

:slight_smile:

Thanks Nigel! You always seem to have a way with explanations that are clear and informative.

PreTech

That was it. Thanks!

So if I write the startdate as “Jan 1, 1900”, compile it and then save the script as a program, that program will run on as many different OS X Macs as possible regardless of language? This is the latest version of the script:

set datenumber to ((current date) - (time to GMT)) - (date "jan 1, 1900")
set datenumber to (datenumber div (days)) + 2

Using div and +2 seems like a sleeker subsitute for using rounding up and +1. It also registers 00.00.00 as a new day which rounding up doesn’t.

Anyway, would this code work on as many OS X Macs as possible?

Thanks for your help.

As long as you write ‘date’ in front of it, so that it compiles on your machine. Yes.

set startdate to date "Jan 1, 1900"

It will appear in your script editor formatted according to the Date and Time preferences on your computer, with “Januari”, etc. But if I open your compiled script on my computer, I’ll see “January”. Others will see “janvier”, “enero”, or whatever the local word is.

But, as I said in an earlier post, this is only true when the date’s compiled into the script. If “Jan 1, 1900” is a string that has to be turned into a date at run time, or if you’re distributing the script as a text file, not all machines will be able to understand it.

Yes ” though I think you only need to add 1 at the end, not 2.

Alright, thanks. The date is indeed compiled directly in the script and it’s not saved or distributed as a string or anything. I just wanted make sure I can distribute the application I compile and save on this computer rather than having to compile and save it on each computer that will use it.

Anyway, +1 should indeed be sufficient but this way I get the same number as the Windows version of the application I’m writing and that’s what matters.

Hello.

This function emulates the excel now() function for Mac, the starting date for the Mac is january the first 1904.

For some *** stupid reason I can’t use english formulas with Excel 2008.


on xlTimeValueOfNow()
	local daterigthNow, timeRigthNow, datenumber
	
	-- 	set daterigthNow to 
	set datenumber to ((current date) -  (date "Friday, January 1, 1904 12:00:00 AM")) / days -- Thanks to Nigel Garvey
	-- You may want to change the date string to some thing that fits with your locale :-)
	-- OR USE THIS: setDateAndTime(1904,1,1,0,0,0)
	return datenumber
end xlTimeValueOfNow

on setDateAndTime(intYear, intMonth, intDay, int23Hour, int59Minutes, int59Seconds)
	local dateObject
	set dateObject to (current date)
	set day of dateObject to 1 -- thanks to Shane Stanley
	set year of dateObject to intYear
	set month of dateObject to intMonth
	set day of dateObject to intDay
	set hours of dateObject to int23Hour
	set minutes of dateObject to int59Minutes
	set seconds of dateObject to int59Seconds
	return dateObject
end setDateAndTime

Hi, McUsr.

No need to do a separate division for the time:


on xlTimeValueOfNow()
	local dateRightNow, timeRightNow, timeDifference
	
	set dateRightNow to (current date)
	set timeDifference to (dateRightNow - (date "Friday 1 January 1904 00:00:00"))
	-- You may want to change the date string to some thing that fits with your locale :-)
	-- OR USE THIS: setDate(1904,1,1,(current date))

	return (timeDifference / days)
end xlTimeValueOfNow

Or, more tersely:


on xlTimeValueOfNow()
	return ((current date) - (date "Friday 1 January 1904 00:00:00")) / days
end xlTimeValueOfNow

If I were writing a date-setting handler like setDate(), I’d pass the time as a parameter and use a fresh date object within the handler itself.

Hello Nigel.

I can see that as clean approach; I just took what I got and by the help of Shane Stanley I got that right, the idea behind the setDate() was to not touch the time, of the passed object since it i might use it to calculate the same time 3 days from now.

I proposed a bug when saying use [b]setDate/b to set the initial date (removed) since the current time of the current date would have been retained. :frowning:

I have written accompanying setTimeOfTheDay() and a setDateAndTime() handlers.

The two handler [b]setDate/b and [b]setTime/b still work as the previous one, as I will avoid creating new date objects that rather weird way of calling current date. Besides that; I can mutate time objects in place.

They are however not overly flexible, since I would have to make a copy of a date object I want to keep.
But: I can retain date and retain time whichever is the one I need. :slight_smile:

setDateAndTime() Is another story since this sets all the parameters, so here I return a brand new date
based on current date to return a new date object.

I used the setDateAndTime() in the function xlTimeValueOfNow() in the post above to remove the proposed bug.


tell application "Quicksilver" to show large type "Thank You!"


on setTimeOfTheDay(int23Hour, int59Minuts, int59Seconds, dateObject)
	set hours of dateObject to int23Hour
	set minutes of dateObject to int59Minuts
	set seconds of dateObject to int59Seconds
	return dateObject
end setTime

on setDateAndTime(intYear, intMonth, intDay, int23Hour, int59Minutes, int59Seconds)
	local dateObject
	set dateObject to (current date)
	set day of dateObject to 1 -- thanks to Shane Stanley
	set year of dateObject to intYear
	set month of dateObject to intMonth
	set day of dateObject to intDay
	set hours of dateObject to int23Hour
	set minutes of dateObject to int59Minutes
	set seconds of dateObject to int59Seconds
	return dateObject
end setDateAndTime

Hi, McUsr.

Given that doing integer maths is faster than setting a date’s properties, a quicker (and shorter) version of setTimeOfTheDay() would be:

on setTimeOfTheDay(int23Hour, int59Minuts, int59Seconds, dateObject)
	set time of dateObject to int23Hour * hours + int59Minuts * minutes + int59Seconds
	return dateObject
end setTimeOfTheDay

The ‘return’ line is obviously just a formality, since the calling process already has the date object.

This terse version of setDateAndTime() also appears to be slightly faster:

on setDateAndTime(intYear, intMonth, intDay, int23Hour, int59Minutes, int59Seconds)
	tell (current date)
		set {day, year, its month, day, time} to {1, intYear, intMonth, intDay, int23Hour * hours + int59Minutes * minutes + int59Seconds}
		return it
	end tell
end setDateAndTime

Note that this method doesn’t work in Snow Leopard with dates prior to October (I think) 1582, owing to to the fact that Apple’s tried to be clever switching to the Julian calendar before then.

Hello

Thanks for your help.

The first version ” should have thought of that! :frowning:

I understand that the second version is shorter to type, and I think I finally realized why it is executing faster:

I believe:
It addresses the result variables indirectly, after having set up the respective addresses initially. Then it just traverses the list and puts each result into correct location by indirect addressing. Hard to explain what I mean:
I prearranges the addresses of the partial results into an array or memory block (at this level). then sets up the result register to point at the address the start address of the memory block contains. That is the stack frame I believe.
Then it just creates a new stack frame which points to the desired end result, while executing a list of function calls, or whatever containing the results.

It must be something like saving the full setup of a stack frame with fetching of addresses to store results in and the like, which makes this faster. It is altogether a whole lot shorter to type, which is always a good thing, as long as it doesn’t blur the intentions.

It is a weird construction, you shouldn’t by chance know somewhere it is documented? :slight_smile:

I really got to get some addresses to the old “inside Macintosh” :slight_smile:

My guess ” without setting it all up again to test it ” is that the speed improvement is also due to single setting of the ‘time’ rather than the individual ‘hours’, ‘minutes’, and ‘seconds’ settings. The lists in the setting-by-list construction usually carry a slight speed handicap.

I don’t, actually. :confused: It’s been around since the early days of AppleScript. It’s normally used to set a whole bunch of variables at once, but here I’ve used it on the properties of a date. The values are assigned in order from left to right and the date adjusts itself after each one. All the values in the right-hand list are obtained before any of them are assigned to the destinations in the left-hand list, which can lead to delightful code like this:

tell (current date)
	set {day, day} to {32, day}
	return it --> One calendar month from this instant.
end tell

-- ie.
-- Tell the date-object returned by the 'current date' function
--   get 32 and the current value of its day
--   set its day to the 32 (overflows it into the following month)
--   and then to the original day number (gives this day next month ”
--   unless of course this day doesn't exist in the following month,
--   in which case it'll be some time in the month after that!)
--   Return the date object in its modified state.
-- end tell

You can also use lists to extract multiple property values from things which have properties:

set {y, m, d, t} to (current date)'s {year, month, day, time}

Or there’s an analogous method with records:

set {year:y, month:m, day:d, time:t} to (current date)
return {y, m, d, t}

By “this method”, I mean the whole idea of changing the current date to another, not just the “list vs. individual lines” detail.

Hello

The constructs are really beautiful, one could really create some heavy stuff with those I have also read that you can add a list to its own tail. The list is a vector but can simulate a nested structure like the one Prolog uses. It is quite possible to really write advanced stuff with AppleScript. (I don’t have to say that this is my favorite language :slight_smile: The hot headed sexy clever versatile Brazilian Woman amongst the programming languages! :smiley: )

I have pondered this a little bit more about speed gains using the set { x,y,z} to {a,b,c} construct and I think that if there is a speed improvement in this, then there is something similar to the reference trick; that AppleScript bypasses some memory address validation in combination with indirect addressing.

Have a nice Sunday and thanks for the information. I finally have universal date routines that works - fast!

Nigel’s “One calendar month from this instant.” surely is delightful code.

Is there a similar delightful code for " “One calendar month previous to this instant.”

Val

Hello Val

Something like this? But no niceties.


tell (current date)
	set its month to (its month as integer) - 1
	return it --> One calendar month before this instant.
end tell

And then we had constructs for previous month and next mont, the similar for years are obvious, as long as we operate before the calendar reform of october 1582.

not that easy, set your system date to some date in january and run the script. :wink: