Make Excel date into DD/MM/YYYY text

Scripting Forums AppleScript | Mac OS X
#1

I have an AppleScript which reads an Excel spreadsheet and writes the data to Apple Aperture. One of the columns in Excel is a date. It’s meant to be in ‘text’ format but it’s not and I can’t seem to convert it. On running my script I get an error

Result:
error "Aperture got an error: Can't make date \"Wednesday, 15 May 2002 01:00:00\" into type rich text." number -1700 from date "Wednesday, 15 May 2002 01:00:00" to rich text

My script sees the cell as a date

date "Wednesday, 15 May 2002 01:00:00"

other data isn’t marked so I assume its plain text.

So do I need a little routine to convert my date into a text string which needs to be in the format DD/MM/YYYY ?

What are my options?

Thanks!

#2

Hi,

assuming you use the UK date format settings in International preference pane,
try this


tell application "Microsoft Excel" to set theDate to value of cell "A1"
if class of theDate is date then
	set dateString to short date string of theDate
end if

the format of short date string conforms to the current short date format settings in International preference pane

#3

Thanks Stefan.

A couple of problems with your solution. First I don’t have Excel here, I’m using Numbers. But in any case even when the date is shown in the format I need, the cell is still a “Date” cell. So I need to convert it to text. I haven’t been able to find a work around using Numbers to convert the date to text.

I think a script to convert is my only option here.

DG

Stop Press: A solution! https://discussions.apple.com/message/16635022#16635022

#4

Sorry you didn’t mention in your first post that you’re using Numbers.

But it works the same way as in Excel


tell application "Numbers" to tell table 1 of sheet 1 of document 1
	set theDate to value of cell "A1"
end tell
if class of theDate is date then
	set dateString to short date string of theDate
end if