I am using some AppleScript to get a date from an Excel spreadsheet. I then need to convert that date into an ICU format to enter into an online form.
To do this I use:
However, the %ICU...% doesn't recognise the clipboard as a date, so returns the stock '1 Jan 1970' value. Does anyone know who to get KM to recognise it as a date for passing on?
TIA
The ICUDateTimeFor token accepts unixtime as input.
To get the proper output from Excel, try a formula like this:
=(A1-DATE(1970,1,1))*86400
I believe that will work, but an alternative approach is to convert the string to Unixtime inside the macro, and this thread explains the technique:
As @avtraino and @Airy have said, the ICUDateTimeFor token needs the date in unixtime.
In your particular case, if the copied dates are always in the format 30/06/1949 then it is possible to break that string up into its separate parts of Day, Month, Year. Then convert that to unixtime and then apply ICU format to it...
The reason the date needs to be broken into Day, Month, Year is that the TIME() function needs the date in the form of 1949,06,30. It won't work with 30/06/1949.
Here is an example where the Magenta Action sets a Variable to 30/06/1949 (but in your case you would set this from the Clipboard by enabling the second Magenta Action instead).
Convert DayMonthYear String into ICU Date.kmmacros (3.9 KB)
How the TIME() function works is explained here.
It's also possible, of course, to derive the unix date stamp integer (and thence copy a string representation of it) in Excel itself:
=(B2 - DATE(1970,1,1)) * 86400
