How to work with observed dates in the format 'dd/mm/yyyy' imported from Excel into MATLAB.

Hello, please I am trying to import a dataset, which contains observed dates, from excel into MATLAB. The dates are in the format 'dd/mm/yyyy'. Example, 22/01/2021. When I store the column containing the dates under 'Datetime', the data cannot be imported. It can be imported when stored under 'Categorical'. In the latter case, I find it difficult to convert using 'datestr' function. Please, apart from changing the date format in excel before importing (example 22-Jan-2021), is there any other faster way to go about this? Meanwhile, I have thousands of observed dates to work with. Hence converting them 'manually' in excel would be time consumimg. Thanks very much.

[Cris LaPierre]

What version of MATLAB are you using? Are you getting any error message or warning when trying to save as datetime?

The best way to work with this data is to import it directly as a datetime. Can you attach a sample file to your post? Use the paperclip icon.

[Victor Lartey]

Please I use MATLAB R2018a.

When I save as datetime, the column is highlighted in yellow colour, and the message I receive is to the effect that the highlighted column cannot be imported.

Please find attached the sample data. The third column is date in the format dd/mm/yyyy. That is where I have the issue.

Thanks.

[Walter Roberson]

filename = 'data_.xlsx';
opt = detectImportOptions(filename);
opt = setvaropt(opt, 3, 'type','datetime');
t = readtable(filename, opt);

[Victor Lartey]

Wow, thanks very much. Very helpful.

Answer 1

[Cameron B]

%simulate your data input. i assume it is a string. if not just change your
%input to make it a string.
b = strings(20,1);
for a = 1:length(b)
    b(a,:) = sprintf('%d/2/2020',a);
end


slashLoc = strfind(b,"/"); %find where the slashes are located
for x = 1:length(slashLoc)
    yourday(x,1) = str2double(extractBetween(b(x),1,slashLoc{x}(1)-1)); %your day
    yourmonth(x,1) = str2double(extractBetween(b(x),slashLoc{x}(1)+1,slashLoc{x}(2)-1)); %your month
    youryear(x,1) = str2double(extractAfter(b(x),slashLoc{x}(2))); %your year
end
newdate = datetime(youryear,yourmonth,yourday); %format new datetime

[Victor Lartey]

Thanks for the answer. It is helpful.