Error reading dates when using readtable function
显示 更早的评论
Hi Guys,
I have some date time values in an Excel (xlsx file). The date times are in a custom format( dd/mm/yyyy hh:mm:ss) as shown below:
01/10/2021 00:00:00
01/10/2021 00:05:00
01/10/2021 00:10:00
01/10/2021 00:15:00
01/10/2021 00:20:00
01/10/2021 00:25:00
01/10/2021 00:30:00
01/10/2021 00:35:00
01/10/2021 00:40:00
01/10/2021 00:45:00
01/10/2021 00:50:00
01/10/2021 00:55:00
01/10/2021 01:00:00
I use the readtable function as follows to get the data from Excel into Matlab:
fileList = dir('*.xlsx');
DATA = [];
for i=2 %numel(fileList)nn
data = readtable([fileList(i).folder '\' fileList(i).name]);
DATA = [DATA; data];
end
For some reason after reading in the data Matlab changes the times by 1 second. For example it changes 01/10/2021 00:15:00 to
01/10/2021 00:14:59 as shown below:
However it doesn't do it for the first three readings. Any idea why this happens???
采纳的回答
N = dateshift(datetime('now'), 'start', 'minute')
N
N - seconds(eps(0))
You can see that for display purposes, MATLAB does not round up if even the smallest possible amount is subtracted from the time.
If I recall correctly, Excel custom date formats round.
I think if you were to look at the underlying time representations in the xls file, you would find that it is not exactly the full seconds you expect.
2 个评论
Peter Perkins
2022-12-13
Walter is on the right track. Excel represents dates/times as whole+fractional number of days, so essentially every timestamp involves round-off. datetime tries to account for that, but doesn't always succeed. A lot depends on what's actually in the xlsx and what the cells are formatted as (text, numeric, datetime). The values in the xlsx may well be off by enough to foil what datetime does. Can't say without a file.
You can always use dateshift to round, but that seems unfortunate. And if you read them as text and convert, round-off doesn't enter in.
Ben Whitby
2022-12-13
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
