Hi everyone!
I found this post as I have had a similar issue trying to write data with timestamps of millisecond precision to Excel. After about a day or two of digging, I believe the issue might actually be in the libmwspreadsheet.dll, but I have not found a way of digging any further. [Note: I am running MATLAB R2019a on Windows 10]
I traced back the issue to the writeXLSFile function, interestingly enough, the 'duration' class data type is written without any rounding issue. Although the exceltime function properly converts from MATLAB 'datetime' class to an Excel date value down to the millisecond precision, writeXLSFile converts this to a complex number to flag that it should be formatted as a datetime cell in Excel (lines 157 to 164 in my case, but similar code exists in lines 116 to 123 of writeXLSFile):
elseif isa(varj,'datetime')
if any(exceltime(varj, dateOrigin) < 0)
varj = cellstr(matricize(varj),[],locale);
else
varj = arrayfun(@(x){complex(x,0)},exceltime(varj));
Simply writing out the datetime in the same manner as a duration carries the proper value and precision. The DLL file must understand string in the following modification as datetime.
varj = cellstr(matricize(varj),[],locale);
Though the default formatting in the output Excel file only displays the time, the date information is still there, just need to convert the cell formatting from mm:ss.0 to mm/dd/yyyy hh:mm:ss.000 or any desired display pattern.
From the above example using the original file yields
tt = timetable([1;2;3],'RowTimes',datetime(2019,6,19,0,0,0:.001:.002,'Fromat','dd-MMM-yyyy HH:mm:ss.SSS'));
writetimetable(tt,'timetable.xlsx')
And with the modified writeXLSFile line
Cheers,
-Eric
2 Comments
Direct link to this comment
https://es.mathworks.com/matlabcentral/answers/467628-time-formate-changes-while-exporting-table-as-excel#comment_715774
Direct link to this comment
https://es.mathworks.com/matlabcentral/answers/467628-time-formate-changes-while-exporting-table-as-excel#comment_715774
Direct link to this comment
https://es.mathworks.com/matlabcentral/answers/467628-time-formate-changes-while-exporting-table-as-excel#comment_715889
Direct link to this comment
https://es.mathworks.com/matlabcentral/answers/467628-time-formate-changes-while-exporting-table-as-excel#comment_715889
Sign in to comment.