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

2 views (last 30 days)
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

Accepted Answer

Ingrid on 28 Oct 2015
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);
Ingrid on 28 Oct 2015
indeed a very valid remark, you just want to remove the values after the point and not use round, sorry for my mistake

Sign in to comment.

More Answers (2)

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

AA on 28 Oct 2015
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


Find more on Dates and Time in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!

Translated by