Search excel file for a specific time interval and store that data as a variable

I have attached an example of the excel file that our data logger puts out. Basically I want to input into a text entry box on the gui the start and end time of a run and have matlab search this excel file to find the data in the rate column that is for that time interval. I then want matlab to search within that data set for specific time stamps (which the user will also have inputted) and store those as variables so that when I plot the total run time I can mark particular event times on the graph with a label based on the user input.

What do I need to accomplish this? I already have the gui made up in guide for the events, and start and stop time, but what format do I need to put the time in so matlab can understand it and match it up with the time in my xlsread data?

Answer 1

[dpb]

Input the gui dates in whatever format you want as the user interface. To find the data in the spreadsheet and make the plots and markers, convert to Matlab serial date numbers via datenum and search on that. Unless the spreadsheet is truly tremendously large, you'll likely have better performance by just reading the whole thing into memory and then selecting the subset(s) of interest.

Depending on how the date fields are stored in Excel you may need to use the conversion to/from the initial dates between Matlab and Excel for their serial dates as shown in the Matlab topic at

http://www.mathworks.com/help/exlink/working-with-dates.html

See

doc datenum % and friends for Matlab serial date numbers and related functions

doc datetick % formatting plot axes with datenums

I'll note this is a good candidate for my "syntactic sugar" helper function iswithin to clean up top-level coding. Say your user has selected/input the two start and end dates and you've gotten the two values as

dn1=datenum(y1,m1,d1,h1,m1,s1);
dn2=datenum(y2,m2,d2,h2,m2,s2);

where the inputs are the beginning and end times, respectively. Then having read in the Excel file, and presuming you have a date string as the first column,

dn=datenum(x(:,1));  % the datenums of the Excel sheet
idx=iswithin(dn,dn1,dn2);
x=x(idx,:);  dn=dn(idx);  % save only the rows within the bounds

Now you can plot as

plot(dn,x(:,selectedcolmns);
datetick('x',...              % set the axis to date; options as desired

In the above the function is

function flg=iswithin(x,lo,hi)
% returns T for values within range of input
% SYNTAX:
%  [log] = iswithin(x,lo,hi)
%      returns T for x between lo and hi values, inclusive

flg= (x>=lo) & (x<=hi);

Copy the above and place it in an m-file by the name iswithin.m and put it in some directory on the matlabpath so it will be found. I have a directory in my installation I've called "utilities" that contains this and a bunch of similar goodies.