PDA

View Full Version : exclude holidays when calculating between dates



Ray.Mason
07-04-2012, 10:42 AM
Hi Guys!

I have the below code that is currently excluding weekends in calculated value. However I also want this to exclude holiday days. I have read stuff about networkdays but I can't make any sense of it. Is there anyway I can amend below to exclude UK holiday days?


.Range("I" & lCurrentPhaseLine).Value = Application.WorksheetFunction.WorkDay(.Range("E" & lCurrentPhaseLine).Value, -10)

Bob Phillips
07-04-2012, 10:48 AM
You need to set up a list of holidays and include that range as the 3rd parameter to Workday.

Networkdays calculates the number of days between two dates, if you just want a date forward or back of a given date, you only need Workday.

Ray.Mason
07-04-2012, 11:03 AM
Thanks for your quick response xld!

Do you mean create a range in excel? eg create a worksheet within same workbook e.g on sheet2 and create range range A1:A15 where I list all holidays dates. How would I then incorporate this range in below?:
.Range("I" & lCurrentPhaseLine).Value = Application.WorksheetFunction.WorkDay(.Range("E" & lCurrentPhaseLine).Value, -10)

Sorry I'm only a VBA novice :)

Bob Phillips
07-04-2012, 11:35 AM
Yes in Excel, and as I said, add the range as parameter 3

.Range("I" & lCurrentPhaseLine).Value = _
Application.WorksheetFunction.WorkDay(.Range("E" & lCurrentPhaseLine).Value, -10, Range("A1:A15"))

Ray.Mason
07-04-2012, 11:40 AM
Awesome! Thank you very much xld :)