Converting dates from Excel Spreadsheet into MATLAB

Hey everyone,

I have a quick question. I am trying to convert date strings from an excel spreadsheet into MATLAB. For example, lets say I have the following:

A1 1/1/2000 12:00 %mm\dd\yyyy hh:mm
A2 1/2/2000 12:05
A3 1/3/2000 12:08

B1 5
B2 6
B3 8

How can I plot the numerical data in column B as the y axis and the date strings as the x axis? I know I use xlsread. I have something like:

[excelDates, Test] = xlsread('date.xlsx');

I know its a relatively simple question, but any help would be greatly appreciated (:

~Sarah~

Answer 1

[Fangjun Jiang]

What value do you get in excelDates after running that xlsread() command? You can plot the data directly and then use datetick('x').

[Value, Time] = xlsread('test.xls')

Time=datenum(Time);
plot(Time,Value);
datetick('x',0,'keepticks');

[Sarah]

Hey! I get no value for excelDates, but the variable Test gets all of the string values.

[Fangjun Jiang]

You should make the B column format as number so the numeric data can be read using xlsread(). [Num, Txt, Raw]=xlsread(). See update in answer.

[Sarah]

Hmmm, I tried that, but then I got this error:

??? Error using ==> plot

Vectors must be the same lengths.

[Fangjun Jiang]

You need to check your Excel file. To test out the datetick() part of the code, use the example data:

Value=[5 6 8];

Time={'1/1/2000 12:00','1/2/2000 12:05','1/3/2000 12:08'};

Time=datenum(Time);

plot(Time,Value);

datetick('x',0,'keepticks');

[Sarah]

So based on my understanding, this is what I have:

clc;

%****************EXCEL SPREADSHEET******************%

Column A

A1: 1-28-2010 20:40

A2: 1-28-2010 20:42

A3: 1-28-2010 20:44

Column B

B1: 5

B2: 7

B3: 3

%******************************************

%********MATLAB CODE*********%

[Data, Time] = xlsread('date.xlsx');

Time=datenum(Time);

plot(Time,Data);

datetick('x',0,'keepticks');

By the way the xlsread function is set up, it returns two outputs: a numeric array (data) and a cell array (Time). The cell array is for strings.

Then, I can use datenum to convert the cell array into a numerical array? and then plot?

I am still getting the same error that I mentioned before. Is it because I am not properly converting my cell array into numerical data?

[Fangjun Jiang]

I suspect you might have some cells in column A or B that contain blank white space. After running [Data, Time] = xlsread('date.xlsx'), check the value of Data and Time in Command Window. Do they have the same size? In you case, it should be 3x1.

[Sarah]

Yeah, I am getting an error because of that:

whos Time results in a 5x1 double

whos Data results in a 0x0 double

Not too sure why...

[Sarah]

Sorry I meant whos Time results in 3x1 double

[Fangjun Jiang]

Delete all the cells below the third row, even though they look empty. Some cells may contain whitespace.

[Sarah]

You were right! :O I deleted the empty cells, and I guess some of it had white space because they work after deleting! Thanks so much!!

I asked everything on a "conceptual level" so I could understand the basics. Now I will apply it to my real problem. Thanks again (:

Now