How to work with observed dates in the format 'dd/mm/yyyy' imported from Excel into MATLAB.
10 views (last 30 days)
Show older comments
Victor Lartey
on 16 Mar 2021
Commented: Victor Lartey
on 23 Mar 2021
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.
4 Comments
Walter Roberson
on 21 Mar 2021
filename = 'data_.xlsx';
opt = detectImportOptions(filename);
opt = setvaropt(opt, 3, 'type','datetime');
t = readtable(filename, opt);
Accepted Answer
Cameron B
on 16 Mar 2021
%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
More Answers (0)
See Also
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!