How to count business hour between timestamps excluding weekend in MATLAB?

Question

I wonder if anyone knows how to count business hour or minute between timestamps in 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 between 08:00 to 17:00 and exclude the weekend. It should be 600 minutes.

Does anyone know how to write a MATLAB function to do it? I could also use python and/or R.

New request:
If the work time change to 9:30-11:30 and 13:00-15:00 for each working day. I wonder if anyone knows how to do it?

Thanks in advance!

Updates:
@gnovice I made some changes of your original code. It works fine for the use of two separate ranges! Wonder how do you feel about that?

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

Unless you have MATLAB's Financial Toolbox (or a third party library) it's probably easier to do this in Python or R. — sco1, Jun 23 '17 at 14:51
you could technically write your own. It shouldn't be too difficult — GameOfThrows, Jun 23 '17 at 15:01
Possibly related: https://stackoverflow.com/questions/2224742/business-days-in-python — sco1, Jun 23 '17 at 15:02

gnovice · Accepted Answer · 2017-07-10T17:26:53.073

The MATLAB solution isn't as ugly as everyone assumes (although it could probably be simplified using the Financial Toolbox). To cut to the chase, here is the solution as a function that accepts two datetime values:

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 all works...

First, we have our starting and ending times as datetime values:

startTime = datetime('22/06/2017 18:00');
endTime = datetime('26/06/2017 09:00');

Now we can create an N-by-2 matrix of datetimes. Each row will be a day covered by the range from startTime to endTime, with the times set to 0:00:00 for now:

dateBlock = repmat((dateshift(startTime, 'start', 'day'):...
                    dateshift(endTime, 'start', 'day')).', 1, 2);

Now we set the first column times to 8:00:00 and the second column times 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, clipping them to the 8:00:00 to 17:00:00 time range:

dateBlock(1, 1) = max(dateBlock(1, 1), startTime);
dateBlock(end, 2) = min(dateBlock(end, 2), endTime);

Next, we remove rows with a datestr day value of 'S' (i.e. a weekend day):

dateBlock((datestr(dateBlock(:, 1), 'd') == 'S'), :) = [];

And finally, we take the column differences, sum them (ignoring negative values), and convert to minutes:

workMins = minutes(sum(max(diff(dateBlock, 1, 2), 0)));

And we get the desired result:

workMins =

   600

EDIT:

Regarding the new request, you can change the function slightly to allow passing in the starting and ending times of a work day like so:

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 your separate work 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 a larger range:

workTotal = work_time(startTime, endTime, 9.5, 15) ...
            - work_time(startTime, endTime, 11.5, 13);

If you'd prefer instead to specify the work day 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');

Thanks a lot! Finally have one in MATLAB! And it works very well! Clean and tidy! — James, Jun 23 '17 at 21:35
Your code works pretty well! But I now need to count the work time as: 9:30-11:30 and 13:00-15:00 for weekday. I wonder if this code can do it as well? Thanks in advance. — James, Jul 09 '17 at 22:11
@James: So, instead of a single range from 8:00-17:00, you want to use two separate ranges? — gnovice, Jul 10 '17 at 06:07
Yes! I wonder if you know how to manipulate the code to do it?? — James, Jul 10 '17 at 12:20
Thanks for your answers! It works well! I also updates my solution in the question above. — James, Jul 10 '17 at 21:19

d.b · Answer 2 · 2017-06-23T19:02:06.967

Here is a 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))
}

USAGE

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

That's a good one! But do you it could count minutes and seconds as well? — James, Jun 23 '17 at 16:01
I meant if the function still works if the ended time as end = "26/06/2017 09:59"? — James, Jun 23 '17 at 17:05

Majo · Answer 3 · 2017-06-23T15:24:52.237

This is not pretty and there must be a classier way, but this should do the trick in base R:

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

Thinking about it, it might be smarter to generate workdays with the desired timespan and add up the result of difftime for each day.

Matt Jewett · Answer 4 · 2017-06-26T14:09:16.260

Here is a custom function that will work to calculate the minutes worked. It's a bit lengthy, but it has a few checks in it which help ensure values are being calculated properly. It also takes the unit as an argument, so you can calculate the total as secs, mins, 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")

Just wanted to give an update to this script. I also added in a check to take into account if Date1 or Date2 fall on a weekend. — Matt Jewett, Jun 26 '17 at 14:10

How to count business hour between timestamps excluding weekend in MATLAB?

4 Answers4