Re: Excel change by 1 month
Re: Excel change by 1 month
- Subject: Re: Excel change by 1 month
- From: Doug McNutt <email@hidden>
- Date: Mon, 15 Dec 2008 09:31:45 -0700
At 00:04 -0800 12/15/08, Paul Berkowitz wrote:
tell application "Microsoft Excel"
repeat with i from 3 to 21
set d to value of range ("A" & i)
set d to my MonthAdd(d)
set value of range ("A" & i) to d
end repeat
end tell
on MonthAdd(d)
set m to ((month of d as integer) + 1)
set ddd to day of d
if m > 12 then
set m to m - 12
set year of d to (year of d) + 1
end if
if {m} is in {4, 6, 9, 11} and ddd = 31 then--AppleScript treats
"Apr 31" as May 1, VBA as Apr 30
set day of d to 30
end if
set month of d to m
if m = 2 and month of d as integer = 3 then --AppleScript treats
"Feb 31" as Mar 3, VBA as Feb 28 or 29
set day of d to 1 -- Mar 1
set d to d - (1 * days) -- last day of Feb
end if
return d
end MonthAdd
This is what I use in Excel. It returns one month from the cell
immediately above while handling all of the special cases. The
formula was copied from cell C45.
=DATE(YEAR(OFFSET(C45,-1,0)),MONTH(OFFSET(C45,-1,0))+1,DAY(OFFSET(C45,-1,0)))
--
--> From the U S of A, the only socialist country that refuses to admit it. <--
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden