How to export numbers and date/times in a matrix?

I have excel files that contains numbers and date/times. In my case I want to export a new matrix that contains values of 3 variables and their common time in separated columns. First I xlsread the excel file and then I identified each variable . I used the command 'intersect' to find common times between variables. My problem is when I put all together in a matrix ,time's column is represented as a number and not as date-time. When I use datestr(time) ,it gives me right the time's column but the rest values of variables convert to ASCII characters. As follow you can see part of my program:

%Create matrix for each variable (value+time)

SP=[dsp tsp];

HR=[dhr thr];

MAP=[dmap tmap];

ICP=[dicp ticp];

%Intersection of the three variables(sp,hr,map) in order return values and common times 

[C1,ISP,IHR] = intersect(SP(:,2),HR(:,2));

[C2,IC1,IMAP] = intersect(C1,MAP(:,2));

final_map=MAP(IMAP);

final_hr=HR(IHR);

final_sp=SP(ISP);

time2=datenum(C2);

time3=datestr(time2);

final=[final_hr final_sp final_map time3];

Any help is accepted!:)

Answer 1

[Guillaume]

Assuming you're on R2016b, the simplest way to solve your problem is to use the modern tools that have been implemented in recent versions:

1. read the excel data as tables with readtable. Since R2013b, there's hardly any reason to use xlsread anymore since readtable is more powerful and usually simpler to use.
2. convert the tables into timetables, using array2timetable (needs R2016b)
3. synchronise your timetables with synchronize and the 'intersection' option.

All in all, your code should be only four lines:

SP = array2timetable(readtable('somefile.xlsx', 'somerange'));
HR = array2timetable(readtable('somefile.xlsx', 'someotherrange'));
MAP = array2timetable(readtable('somefile.xlsx', 'anotherrange'));
result =  synchronize(SR, HR, MAP, 'intersection')  %merge all three tables into one keeping only times common to all three.

[Margarita]

Hey Guillaume,

Firstly, thank you very much for your help. Excuse me for the missing data. The excel file has 4 columns(1st numeric-code of person, 2nd numeric-code of the 4 variables, 3rd value of each variable and 4th the date-time).After 'xlsread' the file, I identify each variable using the command 'find(variable==number). While your idea ,the identification doesn't work with the 'readtable' and I can't use the point 'somerange'.Plus,I have to change all of my m-file. Isn't there any other option?

[Guillaume]

If I understood correctly, then again readtable would be the easiest way to do what you want:

t = readtable('somefile.xlsx', 'B:D');  %read excel file ignoring column A
newt = unstack(t, 2, 1);   %extract each variable in columns
newt(any(isnan(newt{:, 2:end}, 2)), :) = []; %remove rows which don't have common time for all variables

[Margarita]

Hey Guillaume,

I also tried this yet it didn't work. There was the problem "Cannot concatenate the table variables 'x5385220' and 'Var4', because their types are double and cell." . As I need to continue my project I'll leave the time as location . Thank you very much though!