MATLAB xlsread Function to Import Dates

1
votes

thanks for taking a look at my question.

I'm having a peculiar issue importing an xlsx file into MATLAB R2016a (Mac OS X) , more specifically importing dates.

I am using the below code to import my bank statement history from the Worksheet 'Past' in the xlsx file 'bank_statements.xlsx'. A snippet of column 1 with the dates in dd/mm/yyyy format is also included.

[ndata, text, data] = xlsread('bank_statements.xlsx','Past');

Column 1 dates in dd/mm/yyyy format

My understanding is that MATLAB uses filters to distinguish between text and numeric data with these being represented in the 'text' and 'data' arrays respectively whilst 'ndata' is a cell array with everything included. Previously, when running the script on MATLAB 2015a (Windows) the dates from column 1 were treated as strings and populated in the 'text' array, whilst on MATLAB 2016a (Mac OS X) column 1 of the text array is blank. I assumed this was because updates had been made to how the xlsread function interprets date information.

Here's the strange part. Whilst inspecting the text array through the Variables window and referencing in the Command Window shows text(2,1) to be empty, performing the datenum function on this "empty" cell successfully gives the date in a numbered format:

enter image description here

Whilst I can solve this issue by using the ndata array (or ignoring the fact that the above doesn't make sense to me) I'd really like to understand what is happening here and whilst a seemingly empty cell can actually be holding information which operations can be performed on.

Best regards,

Jim

1
excelmatlabdate

1 Answers

0
votes

I was able to replicate your problem and although I can't answer the intricacies of what is happening, I could offer a suggestion. I was only able to replicate it when I was converting a string of non-date text, which leads me to believe that there might be an issue with the way the data was imported.

Instead of:

[ndata,text,data] = xlsread('bank_statements.xlsx','Past');

maybe try and add in the @convertSpreadsheetDates function if you have it, along with the range of values you want to import, i.e.

[ndata,text,data] = xlsread('bank_statements.xlsx','Past','A2:A100','',@convertSpreadsheetDates);

Probably not what you are looking for but it might help!