Working with dates, problem with 10th month import from exl file

I have a problem when loading in a data set where january columns are expressed as yyyy.01, but october is expressed as yyyy.1.

Since the 0 after 1 in october disapperas, matlab gives me aug, sept, jan, nov, when creating a datetime column.

opts = spreadsheetImportOptions("NumVariables", 4);
opts.Sheet = "Data";
opts.DataRange = "A9:D1811";
opts.VariableNames = ["Date", "Var2", "D", "E"];
opts.SelectedVariableNames = ["Date", "D", "E"];
opts.VariableTypes = ["char", "char", "double", "double"];
opts = setvaropts(opts, "Var2", "WhitespaceRule", "preserve");
opts = setvaropts(opts, "Var2", "EmptyFieldRule", "auto");
iedata = readtable("ie_data.xls", opts, "UseExcel", false);
iedata.Date = datetime(iedata.Date,'InputFormat','yyyy.MM');
clear opts

So the output is

head(iedata,12)

ans =

  12×3 table

       Date         D       E 
    ___________    ____    ___

    01-Jan-1871    0.26    0.4
    01-Feb-1871    0.26    0.4
    01-Mar-1871    0.26    0.4
    01-Apr-1871    0.26    0.4
    01-May-1871    0.26    0.4
    01-Jun-1871    0.26    0.4
    01-Jul-1871    0.26    0.4
    01-Aug-1871    0.26    0.4
    01-Sep-1871    0.26    0.4
    01-Jan-1871    0.26    0.4
    01-Nov-1871    0.26    0.4
    01-Dec-1871    0.26    0.4

Answer 1

[Andreas Bull]

I made this soultion and it seems to be working fine to adjust the date that you want with the criteria that the next month number is 11, and current is 1.

% Fix month import issue
for i = 1:length(iedata.Date)
    if month(iedata.Date(i)) == 1 && month(iedata.Date(i+1)) == 11
        iedata.Date(i) = iedata.Date(i) + calmonths(9);
    end
end
clear i