MATLAB: Readtable does not handle excel dates and times properly

datetimeimporting excel datareadtable

I have created a program that reads tables, both CSV and excel. The tables come in different formats, and the program helps the user reformat them to match the formatting of our database. However, I am having trouble reading excel files which have dates and times.
If I open the table in excel, it looks like this.
DATE EVENTNO TIME
30-Apr-2017 84 140158
30-Apr-2017 85 140201
30-Apr-2017 86 140206
30-Apr-2017 87 140211
30-Apr-2017 88 140216
30-Apr-2017 89 140221
30-Apr-2017 90 140226
30-Apr-2017 91 140231
I can look at the format of cells in excel and see that they are the time represents hmmss.
However, when I try to open it in MATLAB, it looks like this
>> opts = detectImportOptions(fname);
>> preview(fname,opt)
>> opts.VariableTypes
ans =
1×3 cell array
{'datetime'} {'double'} {'double'}
'30-Apr-2017 10:01:57' 84 0.584694444444445
'30-Apr-2017 10:02:00' 85 0.584731481481482
'30-Apr-2017 10:02:05' 86 0.584789351851852
'30-Apr-2017 10:02:10' 87 0.584847222222222
'30-Apr-2017 10:02:15' 88 0.584906250000000
'30-Apr-2017 10:02:20' 89 0.584964120370370
'30-Apr-2017 10:02:25' 90 0.585021990740741
'30-Apr-2017 10:02:30' 91 0.585079861111111
The dates have some unexpected times added to them, and the times have values that only come into range if multiplied by 24. Even then it is not a proper time, and I'm not sure how to reconcile it with the date time.
There are actually a lot more columns in my actual tables, and my program does not know what order they will be in or what they will be named, so I cannot hard code a solution.
Is there anyway to automatically detect and fix excel datetime import errors? I prefer to have a single column labeled TIME containing a datetime object.
EDIT: sample file attached

Best Answer

In your file, the data in the first column is a complete date and time, that has been formatted as date only, and formatted for Cameroon, Cameroon is GMT+1
In your file, the data in the third column is time only, formatted as Custom format hmmss
The times in the thrid column appear to be exactly 6 hours after the times in the first column.
But 6 hours after a time, to be consistent, would be an earlier timezone. If the first column is Cameroon time GMT+1, then the third column would have to correspond to GMT+7, which is an Indo-China timezone -- Central Russia (such as Novosibirsk), western China (such as Nepal), eastern India.
You can use readtable() such as
T = readtable(filename);
T.DATE.TimeZone = 'Africa/Algiers'; %starts with no timezone, slap one onto it. Note: WAT has no DST
T.DATE.TimeZone = 'America/New_York'; %switch to local timezone with potential DST
and ignore the TIME column.