How to calculate date length between 2 dates

For the below data, I would like to count the date length between Conf.Time with Trade date

In excel, you can easily do it by using function: weekday(trade date, conftime, holiday), how do you do this in Matlab?

[Adam Danz]

1. Are you rounding to the earliest day? For example, should 12/1/2019 23:59:59 be treated as 12/1/2019 00:00:00 ?
2. Just to be clear, from the values you shared, the number of days should be [1; 0; 1; 0; 0; 0] ?

Answer 1

[Adam Danz]

Replace 'data' with your datetime values in string format. If your datetime values are already in datetime format, replace 'dt' with your datetime values. They must be in a nx3 array or you'll have to make some slight changes.

See inline comments for details.

data = {
    '12/17/2019'  '12/20/2019'  '12/18/2019 23:47:30'
    '12/04/2019'  '12/05/2019'  '12/04/2019 23:48:02'
    '12/17/2019'  '12/20/2019'  '12/18/2019 23:47:30'
    '12/27/2019'  '12/30/2019'  '12/27/2019 23:47:44'
    '12/03/2019'  '12/04/2019'  '12/03/2019 23:43:01'
    '12/27/2019'  '12/30/2019'  '12/27/2019 23:47:44'
    };
dt = [datetime(data(:,1:2),'InputFormat','MM/dd/yyyy'), ...
    datetime(data(:,3),'InputFormat','MM/dd/yyyy HH:mm:ss')];

% Round all datetime values to their earliest date (is this your intension?)
dt = dateshift(dt,'start','day');

% Compute number of days between col 3 and col 1
nDays = days(dt(:,3)-dt(:,1)); 

% Determine if any days are weekends
nWeekends = arrayfun(@(i)sum(isweekend(dt(i,1):day(1):dt(i,3))), 1:size(dt,1))';  

% Subtract weekends
nWeekdays = nDays - nWeekends; 

% Determine if any days are holidays
% List holidays to exclude.  
% You could also use the holidays() function but that would list all holidays
% within range, not only the ones you specified. 
% For example, holidayList = holidays(min(dt(:,1)), max(dt(:,3))); 
holidayList = {'12/24/2019','01/01/2020'}; 
holidayListdt = datetime(holidayList,'InputFormat','MM/dd/yyyy'); 
nHolidays = arrayfun(@(i)sum(isbetween(holidayListdt,dt(i,1),dt(i,3))), 1:size(dt,1))';  

% subtract number of holidays
nWeekdays_noHolidays = nWeekdays - nHolidays;

[Xueyi Li]

Hi Adam,

Thanks again!

Sorry I am a bit lost with the codes of the below, can you explain it? what is i? In my data, column Trade date is netSTP.TradeDate, and column Conftime is break into 2 columns: one with date only- netSTP.Confdate; and one with time ramp only- netSTP.Conftimeonly.

% Determine if any days are weekends

nWeekends = arrayfun(@(i)sum(isweekend(dt(i,1):day(1):dt(i,3))), 1:size(dt,1))';

I seemed to have it wrong with my earlier comments, can you show me to to use this code that you wrote above with the variables that I have at the moment?

I did it like this now:

netSTP.nDays = days(netSTP.Confdate-netSTP.TradeDate)

nWeekends = arrayfun(@(i)sum(isweekend(netSTP.TradeDate(i,1):day(1):netSTP.Confdate(i,1))), 1:size(netSTP,1))'

can you advice if this is correct?

Best regards,

Sherry

[Adam Danz]

This is why it's important to thoroughly describe the inputs in your question :)

The easiest way to transform your inputs so that that they can be used in my solution is to combine them into a matrix. But I still don't know if your datetime values are strings or actual datetime values.

Try

datetimevalues = [netSTP.Confdate, netSTP.TradeDate];

[Adam Danz]

In this line

nWeekends = arrayfun(@(i)sum(isweekend(dt(i,1):day(1):dt(i,3))), 1:size(dt,1))';  

dt(i,1) lists the starting dates and dt(i,2) lists the ending dates.

Perhaps you want to try

nWeekends = arrayfun(@(i)sum(isweekend(netSTP.TradeDate(i,1):day(1):netSTP.Confdate(i,1))), 1:size(netSTP.TradeDate,1))'

[Xueyi Li]

Hi Adam,

Thanks a lot, it took quite a while to load the data but it eventually worked, thanks a lot!

Thanks!

You are the best!

Best regards,

Sherry

[Adam Danz]

Glad I could help!