How do I keep MATLAB from truncating my date numbers?

5 views (last 30 days)
I have data (a lot of data: hundreds of thousand of observations) recorded at one minute intervals over several months in an Excel spreadsheet. The code I use to get the time is:
[NUM,TXT,RAW] = xlsread(filename)
time_string = TXT(:,1);
time = datenum(time_str);
The problem: the time strings in TXT are read correctly (Example: '1/1/2014 12:02:00 AM'), but in the step where they are converted to numbers, MATLAB doesn't keep anything to the right of the decimal.
For example, '1/1/2014 12:02:00 AM' should be "735600.0013888" (found from converting a single date from the time string above - it works for that), but when converting the whole list "time_string", MATLAB truncates this and all the other dates to 735600, which converts to 01-Jan-2014.
Why does it do that? How can I make MATLAB keep the whole number?
  1 Comment
the cyclist
the cyclist on 3 Jun 2014
Are you able to give a small, self-contained example that we can run, that exhibits the problem?

Sign in to comment.

Accepted Answer

the cyclist
the cyclist on 5 Jun 2014
The documentation for datenum states that if you are converting multiple date strings, then they all have to be in the same format. So, if you change your input to
'1/1/2014 12:00:00 AM'
'1/1/2014 12:01:00 AM'
'1/1/2014 12:02:00 AM'
'1/1/2014 12:03:00 AM'
'1/1/2014 12:04:00 AM'
'1/1/2014 12:05:00 AM'
'1/1/2014 12:06:00 AM'
'1/1/2014 12:07:00 AM'
'1/1/2014 12:08:00 AM'
'1/1/2014 12:09:00 AM'
then you will get the answer you expect.
  1 Comment
the cyclist
the cyclist on 6 Jun 2014
FYI, I do also recommend using the second argument, specifying the date format, especially if the vectors are long. On my machine, the code
d = '1/1/2014 12:00:00 AM';
dd = repmat(d,10000,1);
tic datenum(dd);
toc
takes over 4 seconds, while
d = '1/1/2014 12:00:00 AM';
dd = repmat(d,10000,1);
tic
datenum(dd,'mm/dd/yyyy HH:MM:SS AM');
toc
only takes less than 0.2s.

Sign in to comment.

More Answers (4)

the cyclist
the cyclist on 3 Jun 2014
I think this is a longshot, but does it help if you add the second argument, specifying the date string format?
datenum('1/1/2014 12:02:00 AM','mm/dd/yyyy HH:MM:SS')
This should also have the benefit of speeding up that line a lot. (At least, that is my experience.)

Andrei Bobrov
Andrei Bobrov on 3 Jun 2014
time = datenum(time_string, 'mm/dd/yyyy HH:MM:SS AM');

Mike
Mike on 4 Jun 2014
Thanks for the responses!
Adding the other stuff gets me the error message:
>> time = datenum(time_str, 'mm/dd/yyyy HH:MM:SS AM');
Error using datenum (line 181)
DATENUM failed.
Caused by:
Error using dtstr2dtnummx
Failed on converting date string to date number.
Here's an example that illustrates the problem:
>> small = time_str(1:10,:) % Just using a piece of the complete vector of dates
small =
'1/1/2014'
'1/1/2014 12:01:00 AM'
'1/1/2014 12:02:00 AM'
'1/1/2014 12:03:00 AM'
'1/1/2014 12:04:00 AM'
'1/1/2014 12:05:00 AM'
'1/1/2014 12:06:00 AM'
'1/1/2014 12:07:00 AM'
'1/1/2014 12:08:00 AM'
'1/1/2014 12:09:00 AM'
>> datenum(small)
ans =
735600
735600
735600
735600
735600
735600
735600
735600
735600
735600
Oddly:
>> datenum(small(4,:))
ans =
7.356000020833333e+005

Mike
Mike on 6 Jun 2014
That was the problem! Its a little inconvenient that it converts the entire matrix into the format with the least information.
I still have a bunch of data in MATLAB without the longer date format. Here's the code I used to add the rest of the time so I didn't have to reformat and reload all my Excel files:
for date = 1:size(time_str,1)
% use the small length of the incorrectly formatted times to distinguish the two:
if size(time_str{date},2) < 12
% Add the time of 12:00:00 AM to the string
time_str{date} = [time_str{date}, ' 12:00:00 AM']; % note the space at beginning of string
end
end
time = datenum(time_str); % gives results formatted correctly

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!