Readtable 00:00:00 is missing

19 views (last 30 days)
chris_andreas
chris_andreas on 25 Jul 2016
Answered: Motasem Mustafa on 23 Oct 2020
Hi. I have an Excelfile with a.o. a DateTime column. When I import the data in MATLAB with readtable the DateTimes like "15.06.2016 00:00:00" are always imported as just "15.06.2016". This problem just occures with 00:00:00 at the end.
Can anyone help, that the DateTime is imported correctly (with the 00:00:00)? Thanks a lot!

Answers (2)

Titus Edelhofer
Titus Edelhofer on 25 Jul 2016
Hi,
I'm not sure if there is really a problem. I think it's just a question of display. Take a look at this code fragment:
s1 = datetime(2016, 6, 5)
s1 =
05-Jun-2016
s2 = datetime(2016, 6, 5, 0, 0, 0)
s2 =
05-Jun-2016 00:00:00
They are displayed differently but are indeed equal:
isequal(s1, s2)
ans =
1
You might change the display format later on:
s1.Format = 'dd-MMM-uuuu HH:mm:ss'
s1 =
05-Jun-2016 00:00:00
Hope this helps,
Titus
  1 Comment
Mee Youu
Mee Youu on 22 Aug 2020
Edited: Mee Youu on 22 Aug 2020
Of course there is a problem. I am reading datetimes from a CSV file and am trying to convert them to a date number using
[~, ~, myTable] = xlsread('MYfile.csv');
r=cell2dataset(myTable);
mdates=datenum(r.DATETIME,'dd/mm/yyyy HH:MM:SS');
So if the hours are missing in some rows of r.DATETIME then this obviously throws an error. If I don't specify a date format in datenum it does not throw an error but then Matalb is too stupid to consitently recognise European date formats on its own (i.e., it interprets 30/09/2018 as 30 September but then the next one 01/10/2018 as 10 January - how stupid is that?!). I can't believe that a software costing as much as yours is incapable of handling dates correctly.
For anyone who has the same issue and is actually looking for a solution (as Matlab does not even recognise this as a problem) you can fix it by not using xlsread but readtable instead. I.e., the first two lines of code from above should be replaced by:
r=readtable('MYfiles.csv');

Sign in to comment.


Motasem Mustafa
Motasem Mustafa on 23 Oct 2020
I used to have the same issue abd 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

Categories

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