Date conversion, works for one set but not the other, why?

Hi, I have a problem which I hope is a simple fix and I am just not seeing the solution.

I am converting two sets of dates. The first runs continuously from 1st April 2017 - 5th October 2017. I used the following code:

% Convert dates to numbers
datetime.setDefaultFormats('default','yyyy-MM-dd');
Dates = AHDBRH_txt(2:end,2);
DateNumbers = datenum(Dates);
UniqueDateNumbers = unique(DateNumbers); % Result = 189 unique days
DateString = datestr(DateNumbers);
UniqueDateNumbersDateString = datestr(UniqueDateNumbers);
UniqueDateNumbersRow = UniqueDateNumbers';

Which gave me no problems, everything is giving the date it should.

I have a second set of 103 dates of events which fall in-between 1st April and 5th October 2017. When I convert these they are going all over the place - wrong month, wrong day, and the years range from 2017 - 2019. I am using essentially the same bit of code. I have tried reformatting the source data but nothing works, the dates are still wrong. Here is the second bit of code,

%%Convert PLB OB Dates 
datetime.setDefaultFormats('default','yyyy-MM-dd');
OBDates = PLB2017txt(2:end,11);
OBDateNumbers = datenum(OBDates);
OBDateString = datestr(OBDateNumbers);
OBCount = length(OBDates);

I am just not sure what I am missing.. I hope it's obvious and it's just been a long week. Any help or advice would be greatly appreciated.

Thank you!

[Stephen23]

@Chameleon17: today I corrected your code formatting for you. In future you can do it yourself: select the code text, then click the {} Code button. A leading and trailing empty line is also required.

[Chameleon17]

Oh, Thank you- I thought it was hard to read - previously I remember people telling me not to add spaces - I see the code button now though, I will use it in future.

[Rik]

Can you provide an example of which value is wrong? Then we can try to reproduce it.

[KL]

Why not attach a sample text file?

[Chameleon17]

Hi, Okay i attached two text files - the first one 'AHDBDates2' is the type of data going through the first script - I only sent a little bit though as it is too big in its entirety to attach. The second file 'OBDates' is the type of data going through the second script. The first date converts fine - 4th April 2017 = a date number of 736789. After that though it goes off, the second OB date gets converted to date number 737247 which is 06-Jul-2018 and the third gets converted to 737339 which is 06-Oct-2018.

Answer 1

[KL]

I've tried your codes with the sample data provided, it works just fine. In fact, just the first code snippet works fine for both cases.

% Convert dates to numbers
datetime.setDefaultFormats('default','yyyy-MM-dd');
AHDBRH_txt = readtable('OBDates.txt');%readtable('AHDBDates2.txt');
Dates = table2array(AHDBRH_txt);
DateNumbers = datenum(Dates);
UniqueDateNumbers = unique(DateNumbers); % Result = 189 unique days
DateString = datestr(DateNumbers);
UniqueDateNumbersDateString = datestr(UniqueDateNumbers);
UniqueDateNumbersRow = UniqueDateNumbers';

The output is,

UniqueDateNumbersDateString =

    40×11 char array

      '04-Apr-2017'
      '19-Jun-2017'
      '22-Jun-2017'
      '26-Jun-2017'
      '30-Jun-2017'
      '03-Jul-2017'
      '04-Jul-2017'
      '06-Jul-2017'
      '08-Jul-2017'
      '10-Jul-2017'
      '11-Jul-2017'
      '13-Jul-2017'
      '17-Jul-2017'
      '19-Jul-2017'
      '20-Jul-2017'
      '21-Jul-2017'
      '24-Jul-2017'
      '25-Jul-2017'
      '29-Jul-2017'
      '30-Jul-2017'
      '02-Aug-2017'
      '06-Aug-2017'
      '08-Aug-2017'
      '09-Aug-2017'
      '11-Aug-2017'
      '13-Aug-2017'
      '14-Aug-2017'
      '15-Aug-2017'
      '16-Aug-2017'
      '17-Aug-2017'
      '18-Aug-2017'
      '21-Aug-2017'
      '22-Aug-2017'
      '23-Aug-2017'
      '24-Aug-2017'
      '25-Aug-2017'
      '28-Aug-2017'
      '29-Aug-2017'
      '30-Aug-2017'
      '31-Aug-2017'

[Chameleon17]

I have no idea why it won't work for me then - this is frustrating. Thank you for your help though.

[KL]

I have imported data using readtable, how do you import it?

[Chameleon17]

I was using a direct import from the source excel file, a [num,txt,raw] from xlsread. I have switched to the plain txt file now - it works and means I can move on, but I still don't understand what was going wrong with it.

[KL]

I reckoned. It's better to check the imported data before you perform any calculations with it. Anyway, glad it all worked out.

Answer 2

[Peter Perkins]

Chameleon, two things:

1) unless yopu have a reason to use datenum and datestr, use datetimes. In fact, you're call to datetimesetDefaultFormat, which I think you think is controlling the subsequent calls to datenum and datestr, actually has no effect at all.

2) if possible, when reading dates from excel, use readtable, not xlsread. Especially in recent versions of MATLAB,m readtable avoids the vagueries of how Excel wants to convert dates into ambiguous text.

>> t = readtable('OBDates.txt');
>> head(t)
ans =
  8×1 table
       Date   
    __________
    2017-04-04
    2017-06-19
    2017-06-22
    2017-06-22
    2017-06-26
    2017-06-26
    2017-06-30
    2017-07-03
>> ud = unique(t.Date);
>> length(ud)
ans =
    40

In this case (using a very recent version of MATLAB), the text was automatically recogized as dates. This was a "nice" format. In general, you'd want to provide a format and maybe specify that you are reading dates.