Calculating business days between two dataframe columns

Question

I have a data frame that contains two POSIXct columns. How can I go about calculating the number of weekdays between these two columns?

df <- data.frame(StartDate=as.POSIXct(c("2017-05-17 12:53:00","2017-08-31 21:16:00","2017-08-25 13:54:00","2017-09-06 15:47:00","2017-10-15 05:11:00"), format = "%Y-%m-%d %H:%M:%S"),
             EndDate=as.POSIXct(c("2017-06-09 11:57:00","2017-11-29 16:51:00","2017-09-06 15:13:00","2018-01-03 16:22:00","2017-11-17 11:51:00"), format = "%Y-%m-%d %H:%M:%S"))

Possible duplicate of [Calculate the number of weekdays between 2 dates in R](https://stackoverflow.com/questions/5046708/calculate-the-number-of-weekdays-between-2-dates-in-r) — Sathish, Feb 14 '18 at 22:40

mdickey · Answer 1 · 2018-02-15T20:49:36.180

2

Try the bizdays package:

library(bizdays) # Load the package

## Make a calendar that excludes Saturdays and Sundays
create.calendar("Workdays",weekdays = c("saturday", "sunday"))

## Calculate difference in days using the new Workdays calendar
df$bizdays <- bizdays(df$StartDate,df$EndDate,"Workdays")

df$bizdays
[1] 17 63  8 85 24

That returned 17, 63, 8, 85, and 24 business days between the start and end dates you provided. This looks right when I checked the 8 business days between 8/25/2017 and 9/6/2017.

edited Feb 15 '18 at 20:49

answered Feb 14 '18 at 22:25

mdickey

21
5

This seems to take into account US holidays -- does it do holidays in other countries as well? – C. Braun Feb 14 '18 at 22:29
@C.Braun see my solution. It is locale independent, which means it works for any country locale – Sathish Feb 14 '18 at 22:32
@C.Braun The calendar I created actually did not account for holidays, but it looks like the package shows an example with Brazilian holidays. `create.calendar("Brazil/ANBIMA", holidaysANBIMA, weekdays=c("saturday", "sunday"))` (From this vignette https://cran.r-project.org/web/packages/bizdays/bizdays.pdf) – mdickey Feb 15 '18 at 20:41
@M.Dickey I think your calendar is accounting for holidays. Between 8/25/2017 and 9/6/2017 there are actually 9 weekdays, but Labor Day in the US was 9/4/2017, so there were only 8 business days. – C. Braun Feb 15 '18 at 20:48
@C.Braun it must have something to do with what we're considering a full day (whether it includes the start/end date and the timestamp). It looks like this package consistently has 1 day fewer than your solution (except for the first 17 day solution). – mdickey Feb 15 '18 at 21:15
Oh good point. So even though that time period takes place over 9 weekdays, the first and last ones are only half-days(ish). – C. Braun Feb 15 '18 at 21:26

score 2 · Answer 2 · answered Feb 14 '18 at 22:27

Using dplyr:

df %>% 
  dplyr::rowwise() %>% 
  dplyr::mutate(wdays = sum(!weekdays(seq(StartDate, EndDate, by="day")) %in% c("Saturday", "Sunday")))

Source: local data frame [5 x 3]
Groups: <by row>

# A tibble: 5 x 3
  StartDate           EndDate             wdays
  <dttm>              <dttm>              <int>
1 2017-05-17 12:53:00 2017-06-09 11:57:00    17
2 2017-08-31 21:16:00 2017-11-29 16:51:00    64
3 2017-08-25 13:54:00 2017-09-06 15:13:00     9
4 2017-09-06 15:47:00 2018-01-03 16:22:00    86
5 2017-10-15 05:11:00 2017-11-17 11:51:00    25

This makes use of the fact that dates can easily be sequenced, and that because TRUE is equal to one, we can just sum up all of the non-weekend days.

Calculating business days between two dataframe columns

2 Answers2