Excel dates into separate variables

Hello There, I have Date Information (MM/DD/YYY format) in excel that i wish to read in matlab as separate vectors. The code i'm using :

    time_car = xlsread('Climatology.xls','carls','F:F'); 
    [year_car,month_car,day_car] = datevec(num2str(time_car),'yyyymmdd');

|And the error i keep getting reads: |

Error using dtstr2dtvecmx
Failed on converting date string to date number.

Error in datevec (line 117)
            y = dtstr2dtvecmx(t,icu_dtformat);

The exact same code works for the station data from Albuquerque (alb tab i attached file) without any issue. Any help would be appreciated. Thank you,

Answer 1

[Peter Perkins]

Unless you're using a fairly old version of MATLAB, I recommend using readtable and datetimes. It might go something like this:

>> t = readtable('Climatology.xls');

In the version I'm using, DATE came in as a datetime automatically. You may have to tell readtable to do that, using detectimportoptions, or you may have to convert from text to datetime by hand after reading. Still I think you'll be happier. You will need to fix the two-digit issue (which is the file, not really readtable's fault -- although you could probably specify a format using detectimportoptions), and then split DATE into components:

>> t.DATE = t.DATE + calyears(100);
>> [t.YEAR,t.MONTH,t.DAY] = ymd(t.DATE);
>> t = t(:,{'STATION' 'NAME' 'LATITUDE' 'LONGITUDE' 'ELEVATION' 'DATE' 'YEAR' 'MONTH' 'DAY' 'PRCP' 'SNOW' 'TAVG'});

Also recommended to use categorical for repeated text:

>> t.STATION = categorical(t.STATION);
>> t.NAME = categorical(t.NAME);
>> head(t)
ans =
  8×12 table
      STATION           NAME          LATITUDE    LONGITUDE    ELEVATION       DATE        YEAR    MONTH    DAY    PRCP    SNOW    TAVG
    ___________    _______________    ________    _________    _________    ___________    ____    _____    ___    ____    ____    ____
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Jan-2010    2010      1       1      2.4     5         8
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Feb-2010    2010      2       1        0     0       8.7
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Mar-2010    2010      3       1        1     0      16.3
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Apr-2010    2010      4       1        3     0        18
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-May-2010    2010      5       1      5.1     0      22.4
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Jun-2010    2010      6       1      999     0       999
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Jul-2010    2010      7       1     23.1     0      28.9
    USC00291469    CARLSBAD, NM US     32.348      -104.22        951       01-Aug-2010    2010      8       1     39.4     0      28.5

Answer 2

[KSSV]

[num,txt,raw] = xlsread('Climatology.xls') ;
dates = txt(:,6)