Difference between Events in a Limited Time Domain

Looking at this specific data set, I would like to limit the time analysis to working days only, that are from Monday to Friday between 6am and 10pm. My goal is to calculate the difference between two consecutive events. However, a problem arises with events that extend beyond time limits. For example, if an event starts at 21:50 on Monday and the next event starts at 6:50 on Tuesday, the real difference between them is 9 hours but I would like to find a way to calculate this difference so that it falls within the domain specified, resulting in a value of 1 hour. Attached another example.

[Mathieu NOE]

would be nice to share your data and piece of code

Answer 1

[Githin George]

Hi Kostandin Golemi,

I understand you have data in a ‘timetable’ format and would like to find the time difference between every entry in terms of ‘business hours’. You could use the ‘rowfun’ MATLAB function and add a custom logic to resolve the issue as shown below,

% Add 2 columns to the timetable TT - column1 should represent values from 
% next row and column2 should be an exact copy of the DateTime Column of the TT 
TT.TimeProxy = [TT.Time(2:end); TT.Time(end)] 
TT.TimeActual = TT.time 

% Use rowfun function to apply your algorithm for finding actual business 
% hour on each row 
result = rowfun(@yourLogic,TT,"InputVariables",["TimeProxy","TimeActual"]) 

I’ve provided a template for ‘yourLogic’ function which should contain the algorithm to find actual business hours.

function result = yourLogic(TimeProxy,TimeActual) 

% Case 1 - TimeProxy and TimeActual is of Same day 
    % Direct Calculation 
    result = hours(TimeProxy-TimeActual); 

% Case 2 - TimeProxy and TimeActual is of different Day 
    % Step 1 - Find duration from 'TimeActual' to End of Day 
    % Step 2 - while day(TimeProxy) != day(nextBusinessDay) 
        % Add duration of one full working day 
        % The following utility function 'busdate' provides 
        % date of next business day 
        % Busday = busdate('2023-10-20', 1) 
        % x = datestr(Busday) 
    % Step 3 - Add duration from Start of Day to 'TimeProxy' 

    result = hours(resultStep1 + resultStep2 +_resultStep3); 
end 

In ‘Case 2’ the ‘busdate’ function from MATLAB can help in finding next calendar business day.

To know more about ‘rowfun’ and ‘busdate’ functions please refer to the documentation links below,

https://www.mathworks.com/help/matlab/ref/table.rowfun.html#btyj4wy-1-A

https://www.mathworks.com/help/finance/busdate.html#d126e143535

I hope this helps.