importing excel file time format

2 views (last 30 days)
AA
AA on 24 Jun 2016
Answered: Peter Perkins on 2 Nov 2016
suppose the following cell array being imported from the excel file
'Date' 'Time'
'Date' 'Time'
'Mon, Apr-25-16' 0.0763888888888889
NaN NaN
'Mon, Apr-25-16' 0.0763888888888889
'Fri, Mar-25-16' 0.0347222222222222
'Wed, Feb-24-16' 0.0347222222222222
'Tue, Jan-26-16' 0.0347222222222222
'Fri, Dec-25-15' 0.0347222222222222
'Wed, Nov-25-15' 0.0347222222222222
Column two represents time. in the originally excel file it looks like this.
Date Time
Date Time
Mon, Apr-25-16 01:50
Thu, May-26-16 01:50
Mon, Apr-25-16 01:50
Fri, Mar-25-16 00:50
Wed, Feb-24-16 00:50
Tue, Jan-26-16 00:50
Fri, Dec-25-15 00:50
Wed, Nov-25-15 00:50
How can I convert the column 2 into the original time format. Once I have the original time format I want to convert the time into minutes the following way:
Y(:,2)= hour(numFormat)*60 + minute(numFormat);
  1 Comment
Walter Roberson
Walter Roberson on 1 Nov 2016
Could you attach a sample file? Doing this right might depend on exactly how the data is stored.
Also, which MATLAB version are you using, and which operating system?

Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 2 Nov 2016
In R2014b or later, assuming you have a cell array of strings for the date and a vector of doubles for the time,
>> datetime({'Mon, Apr-25-16';'Fri, Mar-25-16'},'InputFormat','eee, MMM-dd-yy') ...
+ days([0.0763888888888889;0.0347222222222222])
ans =
2×1 datetime array
25-Apr-2016 01:50:00
25-Mar-2016 00:49:59

More Answers (4)

Stalin Samuel
Stalin Samuel on 24 Jun 2016

AA
AA on 1 Nov 2016
Edited: Walter Roberson on 1 Nov 2016
matlabtime=[datestr(numOnly(:,1),'HH:MM')];
that gives me a char file.
i cannot convert it using the formula below.
numFormat = datenum(X{:,1},'yyyymmdd HHMMSS');
Y(:,1)= floor(numFormat);
Y(:,2)= hour(numFormat)*60 + minute(numFormat);
Y(:,3:6)= x{:,2:5};

AA
AA on 2 Nov 2016
Can anyone help me?
  1 Comment
Walter Roberson
Walter Roberson on 2 Nov 2016
Nope. Not without answers to what I asked before.

Sign in to comment.


AA
AA on 2 Nov 2016
I have attached a sample. when I import the file then i get decimals instead of time numbers hh:mm. My purpose is to convert the time into minutes.
numFormat = datenum(sample{:,1},'HHMMSS');
Y(:,1)= floor(numFormat);
Y(:,2)= hour(numFormat)*60 + minute(numFormat);
Y(:,3:6)= x{:,2:5};
  1 Comment
AA
AA on 2 Nov 2016
and this is my version 8.5.0.197613 (R2015a)

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!