AppleScript is unable to get a date as string

applescriptms office

Be warned, I am a very new to AppleScript and most programming in general. The basic purpose of the script I've been working on is to read information from Excel files and put it into a FileMaker Pro database. Everything is working well so far, except when attempting to get the value of cells containing dates. Since the dates in the Excel file are already formatted correctly (mm/dd/yy) for the database they are to be put in, I would like to simply read and write them as strings.

The problem arises here, where AppleScript outputs the values as dates in the format (day of the week, month dd, yyyy at 12:00:00 AM) despite my attempt to read them as strings. Below is a shortened version of the script that shows where the problem is occurring.

    tell application "Finder"
    activate application "Microsoft Excel"
    open the first file in folder "Macintosh HD⁩:⁨Users:⁨theUser⁩:Documents⁩:⁨attachments test"
    tell application "Microsoft Excel"
        tell active sheet
            tell used range
                set rowCount to count of rows

                repeat with i from 2 to rowCount

                    set colA to "A" & i & ":A" & i
                    set theDate to get value of range colA as string

                    set rowB to "B" & i & ":B" & i
                    set theTime to get value of range rowB as real

                    set colC to "C" & i & ":C" & i
                    set serialNo to get value of range colC as integer

                    set colD to "D" & i & ":D" & i
                    set pH to get value of range colD as real

                    set colE to "E" & i & ":E" & i
                    set theVolume to get value of range colE as real


                    display dialog "The serial number of row " & i & " is: " & serialNo & " and the pH value is: " & pH & " and the volume is: " & theVolume & " and the date is: " & theDate & " and the time is: " & theTime



                end repeat

            end tell
        end tell
    end tell
end tell

I also tried another method to deal with the problem by setting a variable each for day, month, and year, but ran into another issue where month is not recognized as a purple word (whatever that should be called) and is instead blue (again, I cannot find what these terms are called anywhere). In the image below are three different ways I attempted to get these values from the date in format (day of the week, month dd, yyyy at 12:00:00 AM) so that they could then be reorganized manually.

blue is a good color, but I wanted purple
My only guess for the initial problem is that 'as string' does not work when placed after the other parts of the line, but I have no clue why month is refusing to cooperate. Any guidance would be greatly appreciated!

Best Answer

This may not be a direct solution or quite the answer you are looking for, but this following AppleScript code provide examples of how to convert a given short date string into an actual date, how to extract the elements of the date, edit and set date and time values, and to create your own custom date and time as a string. Hope this helps.

set dateAsText to "9/23/16"

-- converts dateAsText into an actual date
set someDate to date dateAsText -- date "Friday, September 23, 2016 at 12:00 AM"

-- extract the short date from someDate... as a string
set shortDate to short date string of someDate -- "09/23/16"

-- change the time value of someDate from "12:00 AM" to "5:00 PM"
set adjustedDate to date "5:00 PM" of someDate -- date "Friday, September 23, 2016 at 5:00 PM"


-- extract elements from adjustedDate... as strings
set theTime to time string of adjustedDate -- "5:00 PM"

set theWeekday to weekday of adjustedDate -- Friday
set theWeekday to (weekday of adjustedDate) as string -- "Friday"

set theDay to day of adjustedDate -- 23
set theDay to (day of adjustedDate) as string -- "23"

set theMonth to month of adjustedDate -- September
set theMonth to (month of adjustedDate) as string -- "September"

-- edit values of adjustedDate
set day of adjustedDate to 14 -- date "Wednesday, September 14, 2016 at 5:00 PM"
set year of adjustedDate to 1978 -- date "Thursday, September 14, 1978 at 5:00 PM"

------------
set customDate to short date string of adjustedDate & space & theTime -- "09/14/78 5:00 PM"
Related Question