Calculate duration from cell with date hours with Matlab 2013a

I have a cell array of more than 16700 rows and 3 column as in the example below:

'01/01/2014'  '12:00:00'  'AM'
'01/01/2014'  '06:00:00'  'AM'
'01/01/2014'  '10:00:00'  'AM'
'01/01/2014'  '12:00:00'  'PM'
'09/04/2014'  '11:00:00'  'PM'

I would like to calculate the duration of two chosen dates and time (for example the last row-first raw=number of days, hours and seconds). My big problem is that I have a R2013a version of Matlab. So I can't use DateTime, I tried this:

newA = cellfun(@(s) strsplit(s, ' '), DateTime, 'UniformOutput', false); 
DateTimeSep = vertcat(newA{:});
%%Calculs de durée et récupération de données
date=datenum(DateTimeSep(:,1),'mm/dd/yyyy');
d=datestr(date(1256,1)-date(1,1), 'dd')
Time=DateTimeSep(10,2)-DateTimeSep(1,2)% I didn't try to consider the AM PM problem yet....

I get d=04 (which is false) and for time I get: "Undefined function 'minus' for input arguments of type 'cell'"

Answer 1

[Jan]

% [Don't use this - see [EDITED 2] for a nicer solution!]
D = {'01/01/2014'  '12:00:00'  'AM'; ...
     '01/01/2014'  '06:00:00'  'AM'; ...
     '01/01/2014'  '10:00:00'  'AM'; ...
     '01/01/2014'  '12:00:00'  'PM'; ...
     '09/04/2014'  '11:00:00'  'PM'};
DateV = datevec(strcat(D(:, 1), '#', D(:, 2)), 'mm/dd/yyyy#HH:MM:SS');
IsPM  = strcmpi(D(:, 3), 'PM');
pstPM = (DateV(:, 4) < 12 & IsPM);
DateV(pstPM, 4) = DateV(pstPM, 4) + 12;  % 12:01PM ==> 12:01, 1:01PM ==> 13:01
midAM = (DateV(:, 4) == 12 & ~IsPM);
DateV(midAM, 4) = 0;                     % 12:01AM ==> 00:01
DateN = datenum(DateV);

Now the time difference can be calculated by a simple subtraction.

datevec(DateN(end) - DateN(1))  % [EDITED, DateTimeN -> DateN]

[EDITED 2] No, the conversion of the time difference to months and years is not meaningful: It is not clear if the "month" has 28, 29, 30 or 31 days, if it concerns the difference between dates. Better:

Duration     = DateN(end) - DateN(1);
DurationDays = fix(Duration);
Tmp          = rem(Duration, 1);  % Time
DurationHour = floor(Tmp * 24);
DurationMin  = floor(rem(Tmp *  1440, 60));
DurationSec  = round(rem(Tmp * 86400, 60));

Sorry, this is too ugly! Nicer:

D = {'02/14/2014', '10:02:04', 'AM'; ...
     '02/26/2014', '01:00:00', 'PM'};
DateN = datenum(strcat(D(:, 1), '#', D(:, 2), '#', D(:, 3)), ...
                'mm/dd/yyyy#HH:MM:SS#AM');
Duration = DateN(end) - DateN(1);
DDays    = fix(Duration);
[~,~,~, Dhour, Dmin, Dsec] = datevec(rem(Duration, 1));

[Christine Abou Nasr]

You mean datevec(DateN(end) - DateN(1))

Thank you,it finally worked :)

[Christine Abou Nasr]

There is just one thing that's i just found out that is wrong, when I have two date that are not "consecutive" and that the time is AM for the 1rst date and PM for the second (or vice-versa), the duration between the 2 dates is completly wrong because in the vector there is one month that is added. For example:

02/14/2014 10:00:00 AM

02/26/2014 01:00:00 PM

The matlab serial numbers are right (DateN) the first one is 735644.4166667

and the second 735656.54166667

but i get the datevec : 0 1 12 3 0 0

(Matlab considers that there is one month between the 1rst and 2nd date) And it does that to more dates that are not consecutive (AM PM). Do you have an idea to fix this? Thank you again and sorry to bother you with this!

[dpb]

See my note below-- datevec isn't smart-enough to handle differential time on its own; you've got to subtract off the full days (integer part) of the difference and then there's a fractional portion of a day left which can be converted to h,mn,sec. The difference is accomplished by subtraction in the datenum space, but the translation back to elapsed time in days, hours, minutes, seconds has to be handled as days and then the remainder as two operations. That's why TMW did introduce the new duration class which does handle this transparently.

>> dn=datenum(['02/14/2014 10:00:00 AM'
'02/26/2014 01:00:00 PM'],'mm/dd/yyyy HH:MM:SS AM');
>> dt=dn(2)-dn(1)
dt =
 12.1250
>> days=fix(dt);
>> [~,~,~,hr,mn,sc]=datevec(rem(dt,1));
>> num2str([days hr mn sc],'Elapsed: %d days, %02d:%02d:%02d')
ans =
Elapsed: 12 days, 03:00:00
>>

And, we can note that 1/8-th (0.125) of a day is indeed 3 hours.

[Jan]

@Christine: You are right.

D = {'02/14/2014', '10:00:00', 'AM'; ...
     '02/26/2014', '01:00:00', 'PM'};
... my code
DateN(end) - DateN(1)
% >> 12.125  % 12 days and 3 hours

But:

datevec(DateN(end) - DateN(1))
% >> 0     1    12     3     0     0

because the serial datenumber is converted to a date and then the month start with 1 for Januare, not with 0. This means, that the DATEVEC conversion is not meaningful, because a difference in "month" can never specify, if it is a month with 28, 29, 30 or 31 days. Therefore 12.125 is the meaningful result, while a conversion to years and months is not well defined.

See [EDITED 2] in my answer.

[Christine Abou Nasr]

Sorry to bother again, but my data changed, I don't have any AM/PM. I have an error that I don't understand in this part of the code:

newA = cellfun(@(s) strsplit(s, ' '), DateTime, 'UniformOutput', false); 
DateTimeSep = vertcat(newA{:});

Error: Error using vertcat Dimensions of matrices being concatenated are not consistent.

DateTime looks like this:

'01/01/2016 00:10:00'

'01/01/2016 00:20:00'

'01/01/2016 00:30:00'

'01/01/2016 00:40:00'

'01/01/2016 00:50:00'

And newA is full of cell with the right separated values. Thank you again!

[dpb]

Well, that way splits on a a blank instead of parsing the date/time fields. Apparently there's one (or more) record that aren't all the same length. After the split, look at

l=cellfun(@length, newA, 'UniformOutput', false);

and use min() max() or unique() to see who's the odd man out.

That will tell you what is the problem in concatenating. But, the real answer is to use the correct format string and convert directly.

[Christine Abou Nasr]

Right thanks!

Answer 2

[dpb]

If using version prior to introduction of datetime, just use date numbers directly...

>> dn=datenum([char(A{:,1}) char(A{:,2}) char(A{:,3})],'mm/dd/yyyyHH:MM:SSAM'); % to datenumbers
>> dt=dn(end)-dn(1);                       % a difference first-last
>> [y,m,d,h,m,s]=datevec(dt)     % numeric difference in y,m,d,...
y =
   0
m =
   9
d =
   2
h =
  23
m =
   0
s =
   0
>> datestr(dt,'dd HH:MM:SS')  % as string in days HH:MM:SS
ans =
02 23:00:00
>>

ADDENDUM

Hmmm...I thought datestr and datevec were just a little smarter than are--I was thinking they would take the value 246 days and reflect that in the datestr conversion if used a field of days only. But, they wrap internally to months so if want days and hours of elapsed time use

days=fix(dt);

then can convert from

fracdays=dt=days;

with datevec datestr to get the time fields in whichever form desired.