How to create “Date” containers in Matlab, ignoring months and years?

My goal is to extract specific rows from a table (see below) that fall into specific "date" containers, e.g. month-end ( 25th-31st, regardless of the month and year). Date range from Jan-2005 until Aug-2016 and time-step is "weekly".

So far I only managed to create binaries for rows that fall into a specific date period:

% T.Date=[datetime(T.Date, 'InputFormat', 'eee dd-MMM-yyyy')];
% Define date containers
  tlower = datetime(2016,07,25);
  tupper = datetime(2016,07,31); 
% Return ones if date falls in between tlower and tupper 
  Binary = isbetween(T.Date,tlower,tupper)
% If "Binary" returns 1, use this row and stack it into a new table 
% with same headers as table "T". -> How?

How can I create date containers that ignore months and years, returning all rows in a new table that are "month-end" ( 25th-31st of each month) ?

Answer 1

[Guillaume]

Learn to manipulate datetime objects:

Either use the day member function:

filteredtable = T(day(T.Date) > 25, :)

or directly the Day property of datetime:

filteredtable = T(T.Date.Day > 25, :)

[John]

thanks, Guillaume! Very comprehensive and exactly doing what I intended to do

Answer 2

[the cyclist]

Here is one way

% Parameters
DAY_COL = 3;
DAY_THRESHOLD = 25;

% Set up some pretend data
Date = {'5-Aug-2016'; '29-Jul-2016'};
x1 = [0.03; 0.0095];
T = table(Date,x1);

% Extract the components of the date
dv = datevec(T.Date);

% Identify the end of the month days
isEndOfMonth = dv(:,DAY_COL) >= DAY_THRESHOLD;

% Filter to just those days
T_end = T(isEndOfMonth,:);

If you have the Financial Toolbox, then you could use the day command to simplify this. I had to resort to using the datevec command, because I don't have that toolbox.

The details of how you do it may also depend on the format your Date variable inside the table.

[Guillaume]

There's no need to convert the datetime to date vectors. You can directly extract the day from a datetime with the day function in base matlab (no toolbox needed):

T(day(T.Date) > 25, :)

[the cyclist]

I did not realize day was part of base MATLAB now. Old habits die hard. (Also, the first hit on google search found the toolbox version!)

Also, I somehow completely overlooked the code that showed that the Table variable was a datetime object. Time for more coffee, I guess.

[John]

Thanks for your effort and answer, @the cyclist!

Answer 3

[Peter Perkins]

In addition to Guillaume's answer, there's also this:

>> d = datetime('now')
d = 
  datetime
   23-Jan-2017 14:09:32
>> week(d,'weekofmonth')
ans =
     4

John, you've defined "month-end" as "the last seven days of a month". It sounds like that's what you intend, but there might be more complicated ways to define it, for example, "the last calendar week of the month, even if that's partial". So you could compare week(d,'weekofmonth') to this:

>> dEnd = dateshift(d,'end','month') % last day of the month
dEnd = 
  datetime
   31-Jan-2017 00:00:00
>> week(dEnd,'weekofmonth') % number of calendar weeks in this month
ans =
     5

[John]

thanks a lot, Peter Perkins!