Re: Need help with a date/excel applescript please.
Re: Need help with a date/excel applescript please.
- Subject: Re: Need help with a date/excel applescript please.
- From: cougar <email@hidden>
- Date: Sun, 02 Feb 2003 14:39:41 -0500
Hello SA,
I wrote a script a while ago that will do what you need. All you need
to do is get the 2 dates from the Excel cells and plug them to
variables. Then pass each variable to the
ConvertDate function I wrote. Then you can just do the date arithmetic
on it as you please. IE: Date 2 - Date 1 , etc.
Let me know if this helps, doesn't work, etc. ok.
Note : ListToString function does not get used but the StringToList and
ListToString functions I wrote come in pairs :)
Rick Bargerhuff
Programmer / Developer
Multi-platform Technician / Specialist / Web Designer
Personal Email: email@hidden
Personal Website :
http://mywebpages.comcast.net/cougar718
(* ConvertTime from Month/Day/Year hh:mm:ss Format to A Real Date Object
* Programmer : Rick "cougar" Bargerhuff copyright 2000
*)
-- Configuration (Should not need to be edited
property themonths : {"January", "February", "March", "April", "May",
"June", "July", "August", "September", "October", "November",
"December"}
property themonths_index : {"1", "2", "3", "4", "5", "6", "7", "8",
"9", "10", "11", "12"}
property theweekdays : {"Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"}
-- date "Weekday, Month Day, Year hh:mm:ss" -- example of date object
format
my ConvertTime("12/25/2002 12:00:00")
on ConvertTime(thedate_to_convert)
set thedate_to_convert to my StringToList(thedate_to_convert, space)
-- Split thedate into two items, date and time
set thedate to item 1 of thedate_to_convert
set thetime to item 2 of thedate_to_convert
set thedate_parts to my StringToList(thedate, "/")
-- Get each part of the date seperately
set themonth to item 1 of thedate_parts
set theday to item 2 of thedate_parts
set theyear to item 3 of thedate_parts
set date_to_format to (item themonth of themonths) & space & theday &
", " & theyear & space & thetime -- Assemble the entire date w/o weekday
set the_correct_date to my SearchForDateMatch(date_to_format)
return the_correct_date
end ConvertTime
on SearchForDateMatch(thedate)
set temp_date to thedate
repeat with i from 1 to (count of theweekdays)
try
set theweekday to item i of theweekdays
set temp_date to theweekday & ", " & temp_date as string
set temp_date to date temp_date
set isvalid to true
return temp_date
on error
set temp_date to thedate -- reset the temp_date
end try
end repeat
end SearchForDateMatch
-- turn string into list
on StringToList(thestring, thedelim)
set oldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to thedelim
set thestringlist to every text item of thestring
set AppleScript's text item delimiters to oldDelims
return thestringlist
end StringToList
-- turn list into string
on ListToString(thestringlist, thedelim)
set oldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to thedelim
set thestring to thestringlist as string
set AppleScript's text item delimiters to oldDelims
return thestring
end ListToString
On Sunday, Feb 2, 2003, at 13:11 US/Eastern, montana wrote:
Hi everyone-
I'm trying to create an excel template that I can add two different
dates and time into two cells formatted like such:
1/17/03 12:00
The applescript should take these two values, convert them to date
objects, do some calculations and return a date oblect that is the
difference of the first two date objects as text in a new cell on the
spreadsheet.
This is what I have so far with the last line being a test:
tell application "Microsoft Excel"
Activate
set sdate to Cell "B1" as date
set edate to Cell "B2" as date
set diff to edate - sdate
set hoursdiff to diff div hours
set secsLO to diff mod hours
set minsdiff to secsLO div minutes
set answ to hoursdiff & " hours;" & minsdiff & " minutes." as text
--set Cell "B3" to answ
display dialog (sdate & edate & answ) as text
end tell
But when I run this I get a dialog window that says:
Saturday, February 1, 2003 12:00:00 AMSunday,
February 2, 2003 12:00:00 AM24 hours;0 minutes.
This is obviously incorrect and begins at the first "Cell"
importation. Any ideas how to fix this?
Thanks.
SA
:)
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.