How to calculate business hour between timestamps excluding weekends in MATLAB?
I wonder if anyone knows how to count hourly or hourly workday between timestamps at MATLAB
? Let's say I have two timestamps:
started: 22/06/2017 18:00
ended: 26/06/2017 09:00
I want to count the minutes between 08:00
and 17:00
and exclude weekends. It should be 600
minutes.
Does anyone know how to write a function MATLAB
to do this? I could also use python
and / or R
.
New request :
If the working hours change to 9:30-11:30
and 13:00-15:00
for each working day. I wonder if anyone knows how to do this?
Thanks in advance!
Update :
@gnovice I've made some changes to your source code. It's great for two different bands! I wonder how you feel about this?
function workMins = work_time(startTime, endTime)
dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
dateshift(endTime, 'start', 'day')).', 1, 4); %'
dateBlock(:, 1) = dateBlock(:, 1)+hours(9)+minutes(30);
dateBlock(:, 2) = dateBlock(:, 2)+hours(11)+minutes(30);
dateBlock(:, 3) = dateBlock(:, 3)+hours(13)+minutes(00);
dateBlock(:, 4) = dateBlock(:, 4)+hours(15);
dateBlock(1, 1) = max(dateBlock(1, 1), startTime);
dateBlock(1, 3) = max(dateBlock(1, 3), startTime);
dateBlock(end, 2) = min(dateBlock(end, 2), endTime);
dateBlock(end, 4) = min(dateBlock(end, 4), endTime);
dateBlock((datestr(dateBlock(:, 1), 'd') == 'S'), :) = [];
workMins = max(diff(dateBlock, 1, 2), 0);
workMins(:,2) = [];
workMins = minutes(sum(workMins(:)));
end
source to share
MATLAB's solution is not as ugly as everyone thinks (although it could be simplified using the Financial Toolbox ). To move on to pursuit, here is a solution as a function that takes two values datetime
:
function workMins = work_time(startTime, endTime)
dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
dateshift(endTime, 'start', 'day')).', 1, 2); %'
dateBlock(:, 1) = dateBlock(:, 1)+hours(8);
dateBlock(:, 2) = dateBlock(:, 2)+hours(17);
dateBlock(1, 1) = max(dateBlock(1, 1), startTime);
dateBlock(end, 2) = min(dateBlock(end, 2), endTime);
dateBlock((datestr(dateBlock(:, 1), 'd') == 'S'), :) = [];
workMins = minutes(sum(max(diff(dateBlock, 1, 2), 0)));
end
And here's how it works ...
First, we have the start and end times as datetime
values:
startTime = datetime('22/06/2017 18:00');
endTime = datetime('26/06/2017 09:00');
We can now create the N-by-2
datetimes matrix . Each line will be a day spanned from startTime
to endTime
, with the time set to 0:00:00
:
dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
dateshift(endTime, 'start', 'day')).', 1, 2);
We now set the first column time to 8:00:00
, and the second to 17:00:00
:
dateBlock(:, 1) = dateBlock(:, 1)+hours(8);
dateBlock(:, 2) = dateBlock(:, 2)+hours(17);
And now we add startTime
and endTime
as the first and last elements, respectively, trimming them to the time range from 8:00:00
to 17:00:00
:
dateBlock(1, 1) = max(dateBlock(1, 1), startTime);
dateBlock(end, 2) = min(dateBlock(end, 2), endTime);
Then we delete the lines with the datestr
value of the day 'S'
(i.e. on the weekend):
dateBlock((datestr(dateBlock(:, 1), 'd') == 'S'), :) = [];
Finally, we take the column differences, sum them (ignore negative values) and convert them to minutes:
workMins = minutes(sum(max(diff(dateBlock, 1, 2), 0)));
And we get the desired result:
workMins = 600
EDIT:
As for the new request, you can slightly modify the function to allow the transition to the start and end times of the business day as follows:
function workMins = work_time(startTime, endTime, workDayStart, workDayEnd)
dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
dateshift(endTime, 'start', 'day')).', 1, 2); %'
dateBlock(:, 1) = dateBlock(:, 1)+hours(workDayStart);
dateBlock(:, 2) = dateBlock(:, 2)+hours(workDayEnd);
...
And now you can call it with separate working time ranges and add the results:
startTime = datetime('22/06/2017 18:00'); endTime = datetime('26/06/2017 09:00'); workTotal = work_time(startTime, endTime, 9.5, 11.5) ... + work_time(startTime, endTime, 13, 15);
Or subtract a subrange from the larger range:
workTotal = work_time(startTime, endTime, 9.5, 15) ... - work_time(startTime, endTime, 11.5, 13);
If you prefer to instead specify workday times as character arrays, you can write the function like this:
function workMins = work_time(startTime, endTime, workDayStart, workDayEnd)
dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
dateshift(endTime, 'start', 'day')).', 1, 2); %'
workDayStart = datevec(workDayStart);
workDayEnd = datevec(workDayEnd);
dateBlock(:, 1) = dateBlock(:, 1)+duration(workDayStart(4:6));
dateBlock(:, 2) = dateBlock(:, 2)+duration(workDayEnd(4:6));
...
And use it like this:
workTotal = work_time(startTime, endTime, '9:30', '11:30') ...
+ work_time(startTime, endTime, '13:00', '15:00');
source to share
Here is the solution in R
DATA
start = "22/06/2017 18:00"
end = "26/06/2017 09:00"
Function
foo = function(start, end, filter_weekend = TRUE,
daystart = "08:00:00", dayend = "17:00:00", units = "mins"){
#start and end should be POSIXct
require(lubridate)
#Get a sequence of all dates between start and end
df = data.frame(col1 = seq.Date(from = as.Date(start),
to = as.Date(end),
by = "days"))
#Set start of each day at daystart
df$start = ymd_hms(paste(df$col1, daystart))
#Set end of each day at dayend
df$end = ymd_hms(paste(df$col1, dayend))
df$days = weekdays(df$start)
#Set the first day$start to be correct date and time
df$start[1] = start
#Set the last day$end to be correct date and time
df$end[NROW(df)] = end
if (filter_weekend == TRUE){
#Remove weekends
df = df[!df$days %in% c("Saturday", "Sunday"), ]
}
#Remove rows if end is smaller than start (relevant in first and last row)
df = df[df$end > df$start, ]
#Compute time difference for each row
df$time = difftime(time1 = df$end, time2 = df$start, units = units)
#Output
return(sum(df$time))
}
USING
library(lubridate)
foo(start = dmy_hm(start), end = dmy_hm(end))
#Time difference of 600 mins
foo(start = dmy_hms("22/06/2017 14:21:19"),
end = dmy_hms("26/06/2017 06:20:53"),
units = "secs", filter_weekend = TRUE)
#Time difference of 41921 secs
source to share
It's not good, and there should be a cool way, but it should do the trick in the R base:
x <- seq(from = as.POSIXct("22/06/2017-18:00", format = '%d/%m/%Y-%H:%M'), to = as.POSIXct("26/06/2017-09:00", format = '%d/%m/%Y-%H:%M'), by= (60*60))
x <- x[!weekdays(x) %in% c('Saturday','Sunday')]
x <- x[x %in% as.POSIXct(unlist(lapply(unique(as.Date(x)), function(x){paste0(x, ' ', ifelse(nchar(8:16) == 1, as.character(paste0('0', 8:16)), 8:16))})), format = "%Y-%m-%d %H")]
(length(x)-1)*60
With this in mind, it might be wiser to create weekdays at the desired interval and summarize the difftime result for each day.
source to share
Here is a custom function that will work to calculate the minutes completed. It's a bit long, but it has a few checks to help ensure that the values are calculated correctly. It also takes a block as an argument, so you can calculate the total number of seconds, minutes, hours, days, or weeks.
work.time <- function(Date1, Date2, work.start, work.end, unit){
# If dates are equal return 0
if(Date1 == Date2){
return(0)
}
# Check to make sure Date1 is always before Date2
if(Date1 > Date2){
D <- Date1
Date1 <- Date2
Date2 <- D
rm(D)
}
# Get workday start and end timestamps for both days
D1.start <- as.POSIXct(format(Date1, format = paste("%d/%m/%Y", work.start)), format = "%d/%m/%Y %H:%M")
D1.end <- as.POSIXct(format(Date1, format = paste("%d/%m/%Y", work.end)), format = "%d/%m/%Y %H:%M")
D2.start <- as.POSIXct(format(Date2, format = paste("%d/%m/%Y", work.start)), format = "%d/%m/%Y %H:%M")
D2.end <- as.POSIXct(format(Date2, format = paste("%d/%m/%Y", work.end)), format = "%d/%m/%Y %H:%M")
# Calculate value for a full workday
full.day <- as.numeric(difftime(D1.end, D1.start, units = unit))
# Calculate value if dates fall on the same day
if(as.Date(Date1) == as.Date(Date2)){
if(weekdays(as.Date(Date1)) %in% c("Saturday", "Sunday")){
val <- 0
} else if(Date1 >= D1.start & Date2 <= D1.end){
val <- as.numeric(difftime(Date2, Date1, units = unit))
} else if(Date1 >= D1.start & Date2 > D1.end){
val <- as.numeric(difftime(D1.end, Date1, units = unit))
} else if(Date1 < D1.start & Date2 <= D1.end){
val <- as.numeric(difftime(Date2, D1.start, units = unit))
} else if(Date1 < D1.start & Date2 > D1.end){
val <- full.day
} else{
val <- 0
}
return(val)
}
# Calculate value for first workday
if(weekdays(as.Date(Date1)) %in% c("Saturday", "Sunday")){
d1.val <- 0
} else
if(Date1 >= D1.start & Date1 <= D1.end){
d1.val <- as.numeric(difftime(D1.end, Date1, units = unit))
} else if(Date1 > D1.end){
d1.val <- 0
} else if(Date1 < D1.start){
d1.val <- full.day
}
# Calculate value for last workday
if(weekdays(as.Date(Date2)) %in% c("Saturday", "Sunday")){
d2.val <- 0
} else if(Date2 >= D2.start & Date2 <= D2.end){
d2.val <- as.numeric(difftime(Date2, D2.start, units = unit))
} else if(Date2 > D2.end){
d2.val <- full.day
} else if(Date2 < D2.start){
d2.val <- 0
}
# Calculate work value for all workdays between Date1 and Date2
work.days <- sum(!weekdays(seq(as.Date(Date1) + 1, as.Date(Date2) - 1, "days")) %in% c("Saturday", "Sunday"))
# Add up final work value total
work.val <- (work.days * full.day) + d1.val + d2.val
return(work.val)
}
Date1 <- as.POSIXct("24/06/2017 18:00", format = "%d/%m/%Y %H:%M")
Date2 <- as.POSIXct("26/06/2017 09:00", format = "%d/%m/%Y %H:%M")
work.time(Date1, Date2, work.start = "08:00", work.end = "17:00", unit = "mins")
source to share