Inconsistent Dates in Time Series

I have a time series of stock returns with inconsistent dates. Some are in the format dd-mmm-yy and others in the format dd.mm.yyyy. I tried to use the following code to adjust the dates and to get the same date format:

Dates = Dependent_v.Date(:,1);
F = regexp(Dates,'-');
G = cellfun(@isempty,F);
 
if G = 0
  I = datenum(F, 'dd.MM.yyyy');
else
  I = datenum(F, 'dd-MMM-yy');

However, I always get the error code: The expression to the left of the equals sign is not a valid target for an assignment.

Answer 1

[Steven Lord]

If possible use datetime instead of datenum. Let's look at two sample dates in different formats.

Dates = {'27-Jun-19', '13.04.2018'};

Try converting all the Dates using the first format.

DT = datetime(Dates, 'InputFormat', 'dd-MMM-yy')

The datetime function didn't know how to convert the second date using that format, so it returned NaT (Not-a-Time, the datetime equivalent of NaN or missing.) Let's identify the dates that could not be converted with the first format by looking for the NaT values.

otherFormat = isnat(DT);

Fill in the NaT values in DT by converting the corresponding elements in Dates using the second format.

DT(otherFormat) = datetime(Dates(otherFormat), 'InputFormat', 'dd.MM.yyyy')

This generalizes if you have a list of potential formats, just preallocate DT using the NaT function then convert the elements in Dates corresponding to NaT using each format in a for loop over the list of formats. Hopefully at each iteration of the loop the number of NaT values in your array decreases, so you'll have fewer and fewer dates to convert each iteration.

By the way, the reason you received that error in your original code is:

if G = 0

The = operator is for assignment, which is not legal as the condition of an if statement. The == operator tests two expressions for equality.

[AU]

Thank you very much for your detailed explanation. It works now!

[Steven Lord]

Adam Danz called out that "Some of the dates that are in 'dd-mmm-yy' format only have a single digit for the day (example: 1-Dec-11 should be 01-Dec-11) ."

In datetime Format values, using one d describes "Day of the month, using one or two digits" so you'd probably want to use that instead of dd which is "Day of the month using two digits" in your format. The full table of identifiers is in the description of the Format property on the datetime documentation page.

Answer 2

[Adam Danz]

% Create fake data
F = [cellstr(datestr(now-10:now, 'dd-mmm-yy')); cellstr(datestr(now-10:now, 'dd.mm.yyyy'))]; 

% Identify dates in 'dd-mmm-yy' format
idx = ~cellfun(@isempty,regexp(F, '\d{1,2}-[A-z]{3}-\d{2}'));

% do conversions
I = zeros(size(F)); 
I(idx) = datenum(F(idx), 'dd-mmm-yy'); 
I(~idx) = datenum(F(~idx),'dd.mm.yyyy'); 

[AU]

Thank you for your quick answer. I still have the following errors:

Error using datenum (line 189)

DATENUM failed.

Error in UBS_Interview (line 24)

I(~idx) = datenum(F(~idx),'dd.mm.yyyy');

Caused by:

Error using dtstr2dtnummx

Failed to convert from text to date number.

However, my data looks exactly like yours.

[Adam Danz]

Could you provide a sample of your data that is causing the error? You mentioned that it's exactly like mine but I just want to make triple-sure of that.

Also, what version of matlab are you using (execute ver() to find out). I'm using 2019a.

[AU]

I am using Matlab_R22017b and you find my data attached. Thank you.

[Adam Danz]

I just tested my answer in r2017b and there are no problems. How are you importing your data? Are your dates a cell array of strings that look similar to this:

F =
  22×1 cell array
    {'20-May-19' }
    {'21-May-19' }
    {'22-May-19' }
    {'23-May-19' }
    {'24-May-19' }...

[AU]

I imported it using readtable so I guess my data is not in cell array format right?

[Adam Danz]

I see what happened. Some of the dates that are in 'dd-mmm-yy' format only have a single digit for the day (example: 1-Dec-11 should be 01-Dec-11) .

The fix is to accept single-digit format within the regular expression:

idx = ~cellfun(@isempty,regexp(F, '\d{1,2}-[A-z]{3}-\d{2}'));
%                                     ^^

I've updated my answer to include single-digit days in that format.

[AU]

Thank you very much Adam!