Readtable 00:00:00 is missing

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!

Answer 1

[Titus Edelhofer]

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

[Mee Youu]

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');

Answer 2

[Motasem Mustafa]

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