CODEWITHSUNDEEP
rexcel-formula
Euan Ives
Euan Ives

Reputation: 111

How to calculate networkdays minus holidays between 2 dates

I am trying to understand how to apply a function like the NETWORKDAYS in Excel. I spent some time searching and found some examples but cannot get 1 to work.

Example 1 :

library(lubridate)
Date1 <- dmy("01/04/2017")
Date2 <- dmy("28/04/2017")
sum(!weekdays(seq(Date1, Date2, "days")) %in% c("Saturday", "Sunday"))

This works fine but need to remove holidays

Example 2 :

workdays = function(iniDate, endDate, holidays) {
  theDates = seq(from=iniDate,to=endDate,by="day")
  isHoliday = theDates %in% holidays
  isWeekend = (as.POSIXlt(theDates)$wday) %in% (c(0,6))
  return (sum(!isHoliday & !isWeekend))
}

This appears my best bet, however I cannot workout how to create bank holidays vector to apply to function.

How can I use this function, or is there a better way to calculate working days between 2 dates excluding holidays ?

Upvotes: 4

Views: 2194

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

I think you are close: if there is a vector of days from 2017-04-01 to 2017-04-28 from:

allDays <- seq.Date(from=as.Date("2017-04-01"), to=as.Date("2017-04-28"), by=1)

And a vector of holidays from:

easter <- c(as.Date("2017-04-15"), as.Date("2017-04-16"), as.Date("2017-04-17"))

Then you can remove the holidays with the setdiff function:

nonHolidays <- as.Date(setdiff(allDays, easter ), origin="1970-01-01")

Using a similar approach to your first example, weekends are returned with:

weekends <- nonHolidays[weekdays(nonHolidays) %in% c("Saturday", "Sunday")]

And the weekends can be removed from the non-holidays with setdiff again:

nonHolidaysWeekends <- as.Date(setdiff(nonHolidays, weekends), origin="1970-01-01")

So you can wrap that in a function that returns the length of nonHolidaysWeekends:

networkDays <- function(beginDate, endDate, holidayDates) {
  # get all days between beginDate and endDate
  allDays <- seq.Date(from=beginDate, to=endDate, by=1)
  # use setdiff to remove holidayDates and convert back to date vector
  nonHolidays <- as.Date(setdiff(allDays, holidayDates), origin="1970-01-01")
  # find all weekends left in nonHolidays
  weekends <- nonHolidays[weekdays(nonHolidays) %in% c("Saturday", "Sunday")]
  # use setdiff again to remove the weekends from nonHolidays and convert back to date vector
  nonHolidaysWeekends <- as.Date(setdiff(nonHolidays, weekends), origin="1970-01-01")
  # return length of vector filtered for holidays and weekends
  length(nonHolidaysWeekends)
}

d1 <- as.Date("2017-04-01")
d2 <- as.Date("2017-04-28")
# includes Easter Sunday
easter <- c(as.Date("2017-04-15"), as.Date("2017-04-16"), as.Date("2017-04-17"))
networkDays(d1, d2, easter)

There's a similar question here if that is of interest.

Upvotes: 1

Related Questions