I have a table for employees absence dates. I need to calculate absence days( in Business days ) for each employee. I have multiple records for each employee. some of these records are duplicates, embedded or overlapped with other dates or just completely separate dates. I am trying to write a function to calculate the number of days (in business days) accounting for overlap and duplicates. My data looks like this
ID<- as.factor(c(rep(1,3), rep(2,3), rep(3,3)))
Startdate= as.Date(c("1-1-2013", "1-4-2013", "2-2-2013", "3-3-2018", "3-2-2018", "4-5-2018",
"7-5-2016","7-9-2016","7-15-2016"), format = "%m-%d-%Y" )
Enddate = c("1-17-2013", "1-19-2013", "2-13-2013", "3-9-2018", "3-13-2018", "4-18-2018","7-22-2016", " 7-21-2016", "8-3-2016")
df<- data.frame(ID, Startdate, Enddate)
df$Startdate= as.Date(df$Startdate, format = "%m-%d-%Y")
df$Enddate= as.Date(df$Enddate, format = "%m-%d-%Y")
df
ID Startdate Enddate
1 1-1-2013 1-17-2013
1 1-4-2013 1-19-2013
1 2-2-2013 2-13-2013
2 3-3-2018 3-9-2018
2 3-2-2018 3-13-2018
2 4-5-2018 4-18-2018
3 7-5-2016 7-22-2016
3 7-9-2016 7-21-2016
3 7-15-2016 8-3-2016
I can calculate the overlap and the duration using this code
df$duration <- difftime(df$Enddate, df$Startdate , units= "days" )+1
# calculate overlap
df$overlap <- 0
for(i in 2:nrow(df)){
samepat <- df$ID[i]==df$ID[i-1]
curovl <- min(df$Enddate[i],df$Enddate[i-1]) - df$Startdate[i]+1
if(curovl>0 & samepat) df$overlap[i] <- curovl
}
# aggregate duration and overlap
res <- aggregate(duration ~ ID, data=df, sum)
res$overlap <- aggregate(overlap ~ ID, data=df, sum)[,2]
# calculate corrected value
res$corrected <- res$duration - res$overlap
But I need to calculate only business days not total days. I am trying to use bizdays function from bizdays package
library(bizdays)
cal <- create.calendar(name = "mycal", weekdays=c("saturday", "sunday"))
df$duration <- bizdays(df$Startdate, df$Enddate, cal = "mycal")+1
any ideas??