Converting a time field in a table to a usable format
21 views (last 30 days)
Show older comments
David Cynamon
on 25 Oct 2016
Answered: Motasem Mustafa
on 23 Oct 2020
I have data in a table which is in the format '00:00:00:000', representing 'Hour:Minute:Second:Milisecond'.
I want to be able to have this data in a usable format so I can perform operations such as making a sub-table only consisting of entries that took place during a specified hour of the day.
I tried using the 'datevec' function with no success and would love to know if there is a function that can make the data useful.
0 Comments
Accepted Answer
Peter Perkins
on 25 Oct 2016
Assuming you're starting out with something like this
>> x = [1;2;3];
>> t = {'11:59:59:795'; '11:59:59:936'; '12:00:00:714'};
>> T = table(x,s)
T =
x t
_ ______________
1 '11:59:59:795'
2 '11:59:59:936'
3 '12:00:00:714'
what you probably want is to replace s with either a datetime or a duration vector, depending on whether or not your timestamps have a date associated with them. I'll assume not.
You cannot currently convert from text to duration, but it's easy to get there. First create a datetime and then remove the date portion:
>> T.t = datetime(T.t,'Format','HH:mm:ss:SSS')
T =
x t
_ ____________
1 11:59:59:795
2 11:59:59:936
3 12:00:00:714
>> T.t = timeofday(T.t); T.t.Format = 'hh:mm:ss.SSS'
T =
x t
_ ____________
1 11:59:59.795
2 11:59:59.936
3 12:00:00.714
Then you can do selection operations with the duration, such as
>> T(T.t < hours(12),:)
ans =
x t
_ ____________
1 11:59:59.795
2 11:59:59.936
You have R2016a, if you had R2016b you could try the new timetable type. Hope this helps.
3 Comments
Peter Perkins
on 25 Oct 2016
You've typed something wrong somewhere. 'HH:mm:SSS' is not 'HH:mm:ss:SSS'.
More Answers (2)
Alexandra Harkai
on 25 Oct 2016
(Plus timetable allows somewhat similar manipulations, for example getting rows for a given period of time, but not necessarily for 'between 4pm and 5pm on any day', although I may be wrong there.)
By the way, is there a specific problem/error you see using datevec?
2 Comments
Alexandra Harkai
on 25 Oct 2016
newTime = datevec(p,'HH:MM:SS:FFF');
seems to be working (on Windows10, R2016a) for either of these cases:
p = {'00:00:00:027'} % this is a cell
p = '00:00:00:027' % this is not a cell
What are the size and class of your p?
Motasem Mustafa
on 23 Oct 2020
I used to have the same issue and I have posted my question yesterday :
'' Dears,
I am using the code below to do parsing for date-time cells in an MS Excel sheet with date-time form of ( 01/05/2019 00:00) as in the screenshot below.
clc,clear,close all;
[num1,data] = xlsread('Book_new.xlsx','sheet1','A1:A30');
a=datevec(data,'dd/mm/yyyy HH:MM:SS');
date=datestr(datenum(a),'dd/mm/yyyy');
time=datestr(datenum(a),'HH:MM:SS');
Year=datestr(datenum(a),'yyyy');
mm=datestr(datenum(a),'mm');
dd=datestr(datenum(a),'dd');
yy=datestr(datenum(a),'yyyy');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');
When I run the code for example for the 1st 30 readings (half hourly readings) it gives me the following error :
"Error using dtstr2dtvecmx
Failed to convert from text to date number.
Error in datevec (line 123)
y = dtstr2dtvecmx(t,icu_dtformat);
Error in motasem (line 4)
a=datevec(data,'dd/mm/yyyy HH:MM:SS');"
But when I change the range of data to avoid the first reading which contains the time 00:00:00 it works and gives the below output :
Any suggestions please ?
"
The new code that works is using readtable function as follows :
clc,clear,close all;
data = readtable('Book_new.xlsx','Range','A1:A60','ReadVariableNames',false);
A = table2array(data);
yy=datestr(datenum(A),'yyyy');
mm=datestr(datenum(A),'mm');
dd=datestr(datenum(A),'dd');
time=datestr(datenum(A),'HH:MM:SS');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');
Hope this will help you
All the best
0 Comments
See Also
Categories
Find more on Logical 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!