R: finding difference in business days

Question

Having problem calculating the date difference in business days, i.e. exclude weekends like networkdays function in Excel.

Here is my data.

e <- structure(list(date.pr = structure(c(15909, 15933, 16517, 15961, 15974, 15978), class = "Date"), date.po = structure(c(15909, 15933, 15954, 15961, 15974, 15978), class = "Date")), .Names = c("date.1", "date.2"), class = c("tbl_df", "data.frame"), row.names = c(NA, -6L))

Found the "bizdays" package for this task. Which works fine for this one.

> bizdays(e$date.2,e$date.1)
[1]   0   0 563   0   0   0

But my data contains cases when date.2 is before date.1.

e2 <- structure(list(date.pr = structure(c(15909, 15933, 16517, 15961, 5974, 15978, 15978), class = "Date"), date.po = structure(c(15909, 15933, 15954, 15961, 15974, 15978, 15979), class = "Date")), .Names = c("date.1", "date.2"), class = c("tbl_df", "data.frame"), row.names = c(NA, -7L))

Now it gives the following error:

> cal <- Calendar(holidaysANBIMA, weekdays=c("saturday","sunday"))
> bizdays(e2$date.2,e2$date.1,cal)
Error in bizdays.Date(e2$date.2, e2$date.1, cal) : 
  All from dates must be greater than all to dates.

I'm thinking using the ifelse() logic, but it gives me the same error.

> ifelse(e2$date.2 < e2$date.1, NA, bizdays(e2$date.2,e2$date.1,cal))
Error in bizdays.Date(e2$date.2, e2$date.1, cal) : 
  All from dates must be greater than all to dates.

Help appreciated.

http://stackoverflow.com/questions/4559175/r-finding-difference-between-business-days, using businessDaysBetween from package "RQuantLib" solved the problem. — woshishui, Jul 19 '16 at 03:39

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Nweekdays() function is adapted from @J. Won. solution at Calculate the number of weekdays between 2 dates in R

This modified function takes into account of date differences of either positive or negative, whereas the above link has accepted solution for positive date difference.

library("dplyr")

e2 <- structure(list(date.pr = structure(c(16524, 16524, 16507, 16510, 16510, 16524, 16510, 5974), class = "Date"), 
                     date.po = structure(c(16524, 16525, 16510, 16517, 16524, 16510, 16531, 15974), class = "Date")), 
                .Names = c("date.1", "date.2"), class = c("tbl_df", "data.frame"), row.names = c(NA, -8L))

Nweekdays <- Vectorize(
  function(a, b) 
  {
    ifelse(a < b, 
           return(sum(!weekdays(seq(a, b, "days")) %in% c("Saturday", "Sunday")) - 1), 
           return(sum(!weekdays(seq(b, a, "days")) %in% c("Saturday", "Sunday")) - 1))
  })


> e2 %>%
     mutate(wkd1 = format(date.1, "%A"),
            wkd2 = format(date.2, "%A"),
            ndays_with_wkends = ifelse((date.2 > date.1), (date.2 - date.1), (date.1 - date.2)), 
            ndays_no_wkends = Nweekdays(date.1, date.2))

Source: local data frame [8 x 6]

      date.1     date.2   wkd1     wkd2 ndays_with_wkends ndays_no_wkends
      (date)     (date)  (chr)    (chr)             (dbl)           (dbl)
1 2015-03-30 2015-03-30 Monday   Monday                 0               0
2 2015-03-30 2015-03-31 Monday  Tuesday                 1               1
3 2015-03-13 2015-03-16 Friday   Monday                 3               1
4 2015-03-16 2015-03-23 Monday   Monday                 7               5
5 2015-03-16 2015-03-30 Monday   Monday                14              10
6 2015-03-30 2015-03-16 Monday   Monday                14              10
7 2015-03-16 2015-04-06 Monday   Monday                21              15
8 1986-05-11 2013-09-26 Sunday Thursday             10000            7143

> e2 %>% mutate(ndays_no_wkends = Nweekdays(date.1, date.2))

Source: local data frame [8 x 3]

      date.1     date.2 ndays_no_wkends
      (date)     (date)           (dbl)
1 2015-03-30 2015-03-30               0
2 2015-03-30 2015-03-31               1
3 2015-03-13 2015-03-16               1
4 2015-03-16 2015-03-23               5
5 2015-03-16 2015-03-30              10
6 2015-03-30 2015-03-16              10
7 2015-03-16 2015-04-06              15
8 1986-05-11 2013-09-26            7143

Thanks @Sathish. I get your logic with splitting the data and bind it back together. I do think there should be a better way. I can use ifelse(date.1 > date.2, NA, date.2 - date.1) to achieve a simple date difference, but the working day requirement complicated it. — woshishui, Jul 19 '16 at 02:22
Thanks, but does it exclude the weekends? e.g. current Monday - previous Friday should be 1, not 3. — woshishui, Jul 19 '16 at 03:17
thanks again @ Sathish, i think businessDaysBetween() from RQuantLib package does a nice job. — woshishui, Jul 19 '16 at 03:40
@woshishui I added locale independent solution here. http://stackoverflow.com/questions/5046708/calculate-the-number-of-weekdays-between-2-dates-in-r/38450488#38450488 — Sathish, Jul 19 '16 at 22:29

R: finding difference in business days

1 Answers1

Linked