OK-any Excel Scripters out there?


I have a schedule that I create in excel and I have used a VBScript that does the following:

When I type in a date, if that date lands on a Saturday, it changes that date automatically to the Friday before.
Same thing for Sunday’s-IF the date lands on a Sunday, it automatically changes that date to the following Monday.

I really want to do my schedules on the MAC, and was wondering if Applescript could do something like that. I don’t think it would be on formatting the cell, but maybe a script that finds every date in a document that falls on a Saturday and changes that date to the friday before, and finds any date that lands on a Sunday and changes it to Monday after?

I wouldn’t even know where to begin with this one, as I don’t use excel all that often except for some pretty simple stuff.


You might want to use Excel for that, a helper column with =A1+CHOOSE(WEEKDAY(A1,1),1,0,0,0,0,0,-1) should do what you want.

If you want an applescript, this should work

tell application "Microsoft Excel"
	set mySheet to sheet "Sheet1" of workbook "Workbook1.xls"
	set columnOfDates to column 1 of mySheet
	set LastRow to first row index of (get end (cell -1 of columnOfDates) direction toward the top)
	set dataRange to get resize (cell 1 of columnOfDates) row size LastRow
	set formula r1c1 of (get offset dataRange column offset 1) to "=RC1 + CHOOSE(WEEKDAY(RC1,1),1,0,0,0,0,0,-1)"
	set value of dataRange to get value of (get offset dataRange column offset 1)
	clear contents (get offset dataRange column offset 1)
end tell

HI Mike,

This looks promising!
I will keep you posted on how it works out!!
thanks so much!

HI Mike,

OK-I played with this a bit.

The AppleScript is working, but it deletes everything in my sheet other than the first column? I have been trying to figure out how to get it to look at every column in the document, make the changes , and not delete anything, but I have not been successful.

As far as doing it right inside of excel, since I do not use excel all that much, I had some trouble figuring out what a “helper column” was. I looked it up and saw some examples, but I m still having difficulty putting it in this situation.

Anymore help is greatly appreciated… and thanks for all of this so far!!!

Try this

tell application "Microsoft Excel"
	display dialog "Change dates away from weekends on the active sheet." & linefeed & "This can not be un-done."
	set cCells to special cells (used range of active sheet) type cell type constants
	set cAreas to areas of cCells
	repeat with aIndex from 1 to count of cAreas
		set oneArea to (item aIndex of cAreas)
		repeat with i from 1 to count of cells of oneArea
			set oneCell to (cell i of oneArea)
			set oneVal to get value of oneCell
			if class of oneVal = date then
				set thisDay to weekday of (get value of oneCell)
				if thisDay = Sunday then
					set value of oneCell to ((value of oneCell) + (60 * 60 * 24))
				else if thisDay = Saturday then
					set value of oneCell to (value of oneCell) - (60 * 60 * 24)
				end if
			end if
		end repeat
	end repeat
	display dialog "done"
end tell

hi Mike!!!

You are a genius!!!
Thank you so much for all your help…
I have been playing around and doing better with this stuff, but since I really don’t know excel all that well, I got into the dictionary and was completely lost!!!
This is great, I cannot thank you enough :lol: