Separate Date and Time from excel file column

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.

Answer 1

[Azzi Abdelmalek]

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)

[Utsav]

Thanks a lot. This was what I absolutely needed.

Answer 2

[Walter Roberson]

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.