conversion of a date/time column into serial date number and seperate time column

I have a table in table format and it has 6 columns and 6 million rows.

The first column has dates and time in such a format '20050103 014800' which stands for 3rd January 2005 01:48:00.

I want the date of the first column to be converted into a date serial number which can be recognized by matlab (ie. via datenum). The time section I want to put into a seperate adjacent column in the format 00:00-->0, 00:01-->1 up until 23:59-->1439. can anyone help me? thanks

Answer 1

[Ingrid]

use datenum and a format specifier (assuming X is your variable and Y is the requested variable)

numFormat = datenum(X{:,1},'yyyymmdd HHMMSS');
Y(:,1)= round(numFormat);
Y(:,2)= hour(numFormat)*60 + minute(numFormat);

[Thorsten]

round will code the next day if hours >= 12. That will give identical results for, e.g.,

'20050103 124800'
'20050104 004800'

Use floor instead.

[Ingrid]

indeed a very valid remark, you just want to remove the values after the point and not use round, sorry for my mistake

Answer 2

[Thorsten]

 s = '20050103 014800';
 dat = datenum([s(1:8)], 'yyyymmdd')
 min = sscanf(s(10:11), '%f')*60 + sscanf(s(12:13), '%f')

Answer 3

[AA]

thanks to both of you. how do i put the newly generated column 1 and column 2 together as a matrix with the remaining 4 columns in the table so that i have a matrix with 6 columns. i forgot to ask this question. thanks