2

I wish to calculate the intervals between dates. The differences in days should take weekends in account. I have over 200 dates stamps.

For example, the currently displayed time difference between 5th (Tuesday) and 11th (Monday) January are 5 days. I would like to obtain 3 days.

I could manage to get to a solution without excluding Saturday and Sunday with the following code and the packages lubridate and dplyr.

Could you please guide me how to exclude the weekends for calculation? Thank you.

library(lubridate)
library(dplyr)


dates <- c("2021-01-01", "2021-01-04", "2021-01-05", "2021-01-06", "2021-01-11", "2021-01-13", "2021-01-14", "2021-01-18", "2021-01-25", "2021-01-29")

d <- do.call(rbind, lapply(dates, as.data.frame))

dateoverview <- rename(d, Dates = 1)

dateoverview$Dates <- lubridate::ymd(dateoverview$Dates)

datecalculation <- dateoverview %>%
                   mutate(Days = Dates - lag(Dates)) %>% 
                   mutate(Weekday = wday(Dates, label = FALSE))

datecalculation


##         Dates    Days Weekday
## 1  2021-01-01 NA days       6
## 2  2021-01-04  3 days       2
## 3  2021-01-05  1 days       3
## 4  2021-01-06  1 days       4
## 5  2021-01-11  5 days       2
## 6  2021-01-13  2 days       4
## 7  2021-01-14  1 days       5
## 8  2021-01-18  4 days       2
## 9  2021-01-25  7 days       2
## 10 2021-01-29  4 days       6
17757970
  • 141
  • 6
  • 1
    Does this answer your question? [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) – ekoam Jan 10 '22 at 02:24

2 Answers2

2

Probably, there is a function somewhere already doing this but here is a custom one which can help you calculate date difference excluding weekends.

library(dplyr)
library(purrr)

date_diff_excluding_wekeends <- function(x, y) {
  if(is.na(x) || is.na(y)) return(NA)
  sum(!format(seq(x, y - 1, by = '1 day'), '%u') %in% 6:7)
}

datecalculation %>%
  mutate(Days = map2_dbl(lag(Dates), Dates, date_diff_excluding_wekeends))

#        Dates Days Weekday
#1  2021-01-01   NA       6
#2  2021-01-04    1       2
#3  2021-01-05    1       3
#4  2021-01-06    1       4
#5  2021-01-11    3       2
#6  2021-01-13    2       4
#7  2021-01-14    1       5
#8  2021-01-18    2       2
#9  2021-01-25    5       2
#10 2021-01-29    4       6
  • seq(x, y - 1, by = '1 day') creates a sequence of dates between previous date and current date - 1.
  • format(..., "%u") returns day of the week. 1 is for Monday, 7 for Sunday.
  • Using sum(!format(...) %in% 6:7) we count number of days that are present on weekdays.
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • I want to push this comment again. Just to make sure you have seen it. If you have seen it already and would prefer me to ask a new question, please just tell me. Not a problem at all. I am aware that usually I should open a new question, but based on your very customized code I ask here in the comment section. Is there any possibility to exclude specific days? For example, I assume the 12th of January is like weekend and therefore exclude this date, so the difference between 11th and 13th is only 1 day instead of 2. Thank you once again! – 17757970 Jan 31 '22 at 22:16
  • Hi @17757970 , sorry I missed your earlier comment. Please ask this as a new question. Thanks. – Ronak Shah Feb 01 '22 at 01:42
  • Sure. Thank you! – 17757970 Feb 01 '22 at 10:00
2

Another possible solution:

library(lubridate)

# sample data
df = data.frame(Dates = seq(ymd('2021-01-01'),ymd('2021-12-31'),by='days'))
df_weekdays = df %>% filter(!(weekdays(as.Date(df$Dates)) %in% c('Saturday','Sunday')))

#Application to your data 
datecalculation =   datecalculation %>% 
    filter(!(weekdays(as.Date(datecalculation$Dates)) %in% c('Saturday','Sunday')))
thehand0
  • 1,123
  • 4
  • 14