Datenum Resulting with Lower Values for Future Dates

I have a spreadsheet that I read into Matlab with one of the columns containing dates. Matlab treats these as strings so I used the datenum function to convert the strings to serial date numbers.

For certain values it works fine, for example to find the time elapsed between

• A: 3/26/2014 11:48:35 AM
• B: 5/9/2014 2:23:34 PM

the function converts them to

• A: 7.356844920717593e+05
• B: 7.357285996990740e+05

then in essence I use floor(B-A) to round down to get 44 days.

Problem is, sometimes I get negative answers. So for example, to redo the above example, for

• A: 2/14/2014 3:58:41 PM converts to 7.356713568402778e+05
• B: 6/17/2014 8:29:33 AM converts to 7.356153581828703e+05

the answer if done in Excel for B-A is about 122.7 days which should floor to 122, but Matlab gives a difference of -55.9987.

Why is a date further out from Jan 00, 0000 in B providing a smaller serial date value than A? It's giving me negative numbers and messing with my results. Any thoughts? I can't figure out why it is working for some values and not for others. I've read a similar thread on here that simply said that the "format was permuted" but they all seem to have the same format as copy pasted into the above bullets.

[Geoff Hayes]

What is the code that you are using to convert these date strings? If I do

 A = datenum('2/14/2014 3:58:41 PM','mm/dd/yyyy HH:MM:SS AM');
 B = datenum('6/17/2014 8:29:33 AM','mm/dd/yyyy HH:MM:SS AM');
 B - A

 ans =

          122.688101851847

it works as expected.

[Mike]

I'm using

dates(n-1,1) = datenum(alldata(n, 2), 'mm/dd/yyyy HH:MM:SS');

Where 'dates' is the array the datenums are being recorded into sans the first index as that is the row for the spreadsheet labels, alldata is the combined loaded readin and 2 is the column (B) in the spreadsheet with the dates.

When I copy in your hardcoded solution I also get the 122.688...now I'm really confused. It doesn't make sense why when iterating through the loop half the values would convert properly and the others wouldn't.

Edit: Oh wait, I see the correct B value one cell earlier in my dates array, my loop counter is off by one somewhere and it is doing a difference using another patient's earlier dates. Well, at least I know where the problem lies now (need to fix my loop counters), thanks guys!

Answer 1

[Star Strider]

I can’t reproduce the error.

When I code:

A = datenum('2/14/2014 3:58:41 PM', 'mm/dd/yyyy HH:MM:SS PM');
B = datenum('6/17/2014 8:29:33 AM', 'mm/dd/yyyy HH:MM:SS AM');
DD = B-A

I get:

DD =

     122.6881e+000

that would round down to 122.

[Star Strider]

You need to add the ‘AM’ to the format string in your conversions or the datenum function will assume 24-hour time. (It’s best to use 24-hour time anyway to avoid confusion, but if you have it in AM/PM format, you have to tell datenum.)