Separate Date and Time from excel file column

1 次查看（过去 30 天）
Utsav
Utsav 2016-1-15
评论： Utsav 2016-1-15
DD22122015000001
I have time column in excel as
DD22122015000101
DD22122015000201
DD22122015000301
etc
they are 1 day file 22/12/2015 at 00:01:01, 00:02:01 , 00:03:01 etc and corresponding parameters
I wish to separate this column of time into two columns dd/mm/yyyy and 00:01:01 ...etc
any help will be highly helpful.

请先登录，再回答此问题。

采纳的回答

Azzi Abdelmalek
Azzi Abdelmalek 2016-1-15
v={'DD22122015000101';'DD22122015000201';'DD22122015000301'}
d=cellfun(@(x) [x(3:4) '/' x(5:6) '/' x(7:10)],v,'un',0)
t=cellfun(@(x) [x(11:12) ':' x(13:14) ':' x(15:16)],v,'un',0)

更多回答（1 个）

Walter Roberson
Walter Roberson 2016-1-15
编辑：Walter Roberson 2016-1-15
To get the values as datenums:
date_column = 5; %for example
[num, txt] = xlsread('TheFile.xls');
datecell = txt(:,date_column);
dates = datenum(regexprep(datecell, '^DD', ''), 'ddmmyyyyHHMMSS');
The regexprep() is there to throw away the 'DD' from the beginning of the string.
You can compute with the dates or you can datestr() to get text.
If you do not care about the numeric representation of the dates and just want to break it up into two columns, then
date_column = 5; %for example
[num, txt] = xlsread('TheFile.xls');
datecell = txt(:,date_column);
datesfmt = regexprep(datecell, '^\s*(?:DD)(\d\d)(\d\d)(\d\d\d\d)(\d\d)(\d\d)(\d\d)\s*', '$1/$2/$3 $4:$5:$6');
dates = regexp(datesfmt, ' ', 'split');
dates = vertcat(dates{:});
datesfmt would have the elements reformatted but single string per line, not broken into two columns. The line after that splits each line into the two columns, but the result is an N x 1 cell each of which is a 1 x 2 cell. The last of the lines recreates it as an N x 2 cell, so dates(:,1) is the date column and dates(:,2) is the time column.

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by