Code to change dates based on weekend and holiday criteria

[gbloemke]

I have created a worksheet that shows dates in 2013 in Column A. I need VBA code that will subtract 3 days from that date and put the new date in Column B (this is a simple subtraction formula that I have already created). However, the tricky part is that if that new date falls on a Saturday, Sunday, or Holiday, I need to subtract one additional day. Any help appreciated. Here's the code I already have:

Sheets("Sheet1").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-3"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B215"), Type:=xlFillDefault

Here's some of the spreadsheet with columns A and B and rows 1-29:

Old Scheduled Completion Date New Scheduled Completion Date
6/20/2013 6/17/2013
6/21/2013 6/18/2013
6/22/2013 6/19/2013
6/23/2013 6/20/2013
6/24/2013 6/21/2013
6/25/2013 6/22/2013
6/26/2013 6/23/2013
6/27/2013 6/24/2013
6/28/2013 6/25/2013
6/29/2013 6/26/2013
6/30/2013 6/27/2013
7/1/2013 6/28/2013
7/2/2013 6/29/2013
7/3/2013 6/30/2013
7/4/2013 7/1/2013

Greg

 

[PHV]

And how your formula should be aware of holidays ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mintjulep]

So test for holidays.

http://www.cpearson.com/excel/holidays.htm

 

[zelgar]

You might be interested in the WORKDAY function. You'll need to set up what the list of holidays (I'd suggest making listing them out in your spreadsheet and naming the range) to get things to work.

 

[gbloemke]

Mintjulet, that link helped a lot. That, along with the post from zelgar, solved my issue. Thanks. I was able to find what I needed.