Excel Date Conversion into MATLAB

I have an excel file with 20,000 dates listed in one column in the form dd/mm/yyyy. I need to import these dates into MATLAB and convert them into a 20,000x2 matrix with month in the first column and year in the second column. I currently have code written to index an excel file I created with this form, but I need to use an excel file in which the dates are in the form dd/mm/yyyy and convert it. Example: the date 1/30/2017 (in excel) should be in the form (in MATLAB) so I can index it with the code I have written. Month (in the first column) Year (in the second column) 1 17

[dpb]

"...so I can index it with the code I have written."

I'd suggest just convert to Matlab datetime and use its features will be so much simpler to deal with that trying to reuse Excel code is a waste. isbetween, ismember and the logical operators are all datetime -aware.

Answer 1

[Star Strider]

The xlsread function will return your dates as a cell array of strings in a column. Assuming that you have already isolated that column (as ‘ExcelDate’ here), see if this does what you want:

The datenum function should parse the dates correctly on its own:

dv = datevec(datenum(ExcelDate));                                       % Date Vectors
MATLAB_Date = [dv(:,2)  rem(dv(:,1), 2000)];                            % Desired Output

If for some reason it doesn’t this will work as an alternative:

ExcelDate = {'1/30/2017'; '1/31/2017'; '2/1/2017'};
date_parse = cellfun(@(x)regexp(x, '/', 'split'), ExcelDate, 'Uni',0);  % Get Numbers (Cell)
MATLAB_Date = cellfun(@(x)str2double(x([1 3])), date_parse, 'Uni',0);   % Convert To Numeric
MATLAB_Date = reshape([MATLAB_Date{:}], 2, [])';                        % Create Matrix
MATLAB_Date(:,2) = rem(MATLAB_Date(:,2), 2000);                         % Get Only Last 2 Year Numbers

MATLAB_Date =
     1    17
     1    17
     2    17

Both produce the same output.

[Nicholas Qassis]

So now the problem I'm having is that the original file is a .csv file. When I save it as an .xlsx and try to upload it to MATLAB, the dates are converted to numbers like '41303'. Your solution requires that the dates be in the 'mm/dd/yyyy' format. How do I resolve this issue? It looks like what you posted above will work, but I'm not sure. Thanks in advance!

[Star Strider]

You didn’t post your file, so I went with the information you provided. You said that your date data were in the string format you posted.

You can certainly read (import) your ‘.csv’ file with xlsread. My code will probably work with xlsread output from a ‘.csv’ file.

If the date you posted is in Excel serial date format instead, you can import it as:

q = 41303;
dt = datetime(q, 'ConvertFrom','excel')

dt = 

    datetime

     29-Jan-2013 00:00:00

Since I now have no idea what you’re doing, and I may be chasing a still-moving target, I’ll stop here and await further details.

[Nicholas Qassis]

I've attached some screenshots so you can hopefully see the issue. When it is saved as a .csv, the dates are in string format. When the document is saved in an .xlsx format, the dates are in serial number format I believe. Here is the code I am working with. Keep in mind this is just to index the year, I have a separate function to index by month.

function [PermitsPerYear]=YearIndex_er3309(Year)
  ds=xlsread('Building_Permits.csv','B1:B19000');
  %retrieving the data from the excel worksheet
  dv = datevec(datenum(Building_Permits));      % Date Vectors
  DataConversion = [dv(:,2)  rem(dv(:,1), 2000)];
  %if statements for the different years the user can select
  %can be idexed with logical operators
  if Year==15 || Year==2015
    PermitsPerYear=sum(DataConversion(:,2)==15);
  elseif Year==16 || Year==2016
    PermitsPerYear=sum(DataConversion(:,2)==16);
  elseif Year==17 || Year==2017
    PermitsPerYear=sum(DataConversion(:,2)==17);
  end
end

I've also encountered these errors:

Warning: Range cannot be used in 'basic' mode.  The entire sheet will be loaded. 
> In xlsread (line 202)
In YearIndex_er3309 (line 4) 

Error using xlsread (line 249)
Unable to read XLS file /Users/hockeyman0715/Documents/MATLAB/Building_Permits.csv. File is not
in recognized format.

Error in YearIndex_er3309 (line 4)
ds=xlsread('Building_Permits.csv','B1:B19000');

If I try to upload with the .xlsx format, here is my code:

function [PermitsPerYear]=YearIndex_er3309(Year)
  ds=xlsread('Building_Permits.xlsx','B1:B19000'); %retrieving the data from the excel worksheet
  dv = datevec(datenum('Building_Permits.xlsx'));  % Date Vectors
  DataConversion = [dv(:,2)  rem(dv(:,1), 2000)];
  %if statements for the different years the user can select 
  %can be idexed with logical operators
  if Year==15 || Year==2015
    PermitsPerYear=sum(DataConversion(:,2)==15);
  elseif Year==16 || Year==2016
    PermitsPerYear=sum(DataConversion(:,2)==16);
  elseif Year==17 || Year==2017
    PermitsPerYear=sum(DataConversion(:,2)==17);
  end
end

Results in these errors:

Error using datenum (line 181)
DATENUM failed.

Error in YearIndex_er3309 (line 6)
dv = datevec(datenum('Building_Permits.xlsx'));  % Date Vectors

Caused by:
  Error using datevec (line 212)
  Failed to lookup month of year.

[Star Strider]

I suggest you post all or part of the file here so I can experiment with it and see if I can write code to import the dates correctly.

Screenshots aren’t going to help.

Also, Firefox is currently unstable and crashes my computer when Firefox crashes. This causes delays.

[Nicholas Qassis]

Here is the file. Keep in mind the only column I'm concerned with is the "Permits Issued" column. I'm not allowed to make any changes to the excel file itself.

[Nicholas Qassis]

Here are the .m files as well.

[Star Strider]

For some reason, datenum doesn’t want to vectorize the dates in column 2, so I went with a loop.

The Code —

[d,s] = xlsread('Building_Permits.csv');
for k1 = 2:20
    dv = datevec(datenum(s(k1,2)));
    Permit_Issued_Date(k1,:) = [dv(:,2)  rem(dv(:,1), 2000)];
end

You can do the same with the other dates. You will likely have to do them column-by-column, either in the same loop or a different loop. I would save all the results you extract and convert to a ‘.mat’ file so you can import them much more effeciently later. See the documentation on save, matfile, and load to understand ‘.mat’ files if you’ve not used them before.

[dpb]

"..., datenum doesn’t want to vectorize the dates in column 2,"

Because there are a number of missing values...

 >> >> sum(cellfun(@(x) length(x),t(2:end,2))==0)
ans =
   703
>> length(t)
ans =
       45589
>>

What does OP propose to do for these? The loop won't convert them, either...

Answer 2

[dpb]

Are the Excel data stored as dates or as strings? If they're date types they'll be read by xlsread as Excel serial numbers. In that case, use

datm=datetime(ExcelDate,'ConvertFrom','excel');  % keeping S Strider's nomenclature

If they're strings, to make it clear to datenum and also speed up processing, tell it the format...

dn=datetime(ExcelDate,'InputFormat','MM/dd/yyyy')');

See

doc datetime  % and friends for creating and using...

Would be interested to see the functions/code you've written to compare to what I'm thinking would work simpler...

ADDENDUM

Based on comment, I'm not yet a believer... :)

>>  [n,t,r]=xlsread('BPermit.csv');  % load your file
>> ix=(cellfun(@(x) length(x),t(:,2))~=0);  % mark missing dates to skip
>> ix(1)=false;                             % skip over header row 
>> da(ix)=datetime(char(t(ix,2)),'inputformat','MM/dd/yyyy');  % datetime array
>>

Now your function could be something like...

function annualPermits=YearIndex_er3309(dates,Year)
% Return number of permits found in given calendar year
% Usage:
%   annualPermits=YearIndex_er3309(Dates,Year)
% returns permits in a given year given
%      Dates -- datetime vector of dates in database
%      Year  -- desired year as yyyy or yy (assume 2000 epoch)

if Year<100, Year=Year+2000; end  % fixup if yy form
% any other error checking desired here (or before)...

annualPermits=sum(year(dates)==Year);

As Star says, read the data and do the conversion only once, then use the database. Anyway, the above would let you do things like--

>> arrayfun(@(yr) disp([yr sum(year(da)==yr)]), [2010:2017].')
      2010        3887
      2011        4772
      2012        5771
      2013        4557
      2014        6849
      2015        8541
      2016        9218
      2017        1288
>>

I've not read the rest of the code, but I can't believe it can't be simpler to make the conversion now...convenience going forward, particularly the newer datetime class as it has the builtin properties/methods like year above that avoids the explicit year/month hassles of datevec with datenum will quickly override a little transition effort now.

Again, however, seems as though a first order of business would be to clean up the database for the missing values...the above works around it to make code run but isn't particularly clean in doing so.

ADDENDUM 2:

OK, I did read the script--can't emphasize enough to read the data first, then ask user what to do with it. While may be a very short initial startup, it'll be much more responsive later when don't have to retrieve data after the menu choice. Anyways, presuming have done as shown above, then

...
x=input('Enter the numerical months ...');
y=input('Of what year? ');
%call month index function
[permits,PermitsPerMonth]=MonthlyPermits_ex1419(x,y)

can become something like

mn=input('Enter the numerical months ...');  % use names that at least
yr=input('Of what year? ');                  % have some bearing on value
% call month index function
[permits,monthlyPermits]=MonthlyPermits_ex1419(da,perms,yr,mn)

and the function something on the order of--

[permits,monthlyPermits]=MonthlyPermits_ex1419(Dates,Permits,Year,Months)
% Return number of permits found in given months for a calendar year
% Usage:
%   permits=MonthlyPermits_ex1419(Dates,Permits,Year,Months)
%     returns permit numbers in a given year and set of months
%
%   [permits,monthlyPermits=MonthlyPermits_ex1419(Dates,Permits,Year,Months)
%     also returns total numbers permits in respective months
% Inputs:
%      Dates   -- datetime vector of dates in database
%      Permits -- cellstr vector of permits in database
%      Year    -- desired year as yyyy or yy (assume 2000 epoch)
%      Months  -- desired months in year as vector of numeric, 1-12

if Year<100, Year=Year+2000; end  % fixup if yy form
% any other error checking desired here (or before)...

isYr=(year(Dates)==Year);
isOK=isYr.*(ismember(month(Dates),Months));
permits=Permits(isOK);
if nargout==1, return,end
monthlyPermits=zeros(length(Months),1);
for i=1:length(Months)
  monthlyPermits(i)=sum(isYr.*(month(Dates)==Months(i)));
end

[Nicholas Qassis]

see my comments above. I know it's not the most efficient way to do this, but I have other functions in my code that would make changing things more difficult.

[dpb]

OK, after the second function the datetime class didn't help directly as much as I thunk it would...seems like the simplest coding would be to keep the year/month for each date column. I didn't mess with it any further but possibly as a categorical in a table would let you use grouping variables effectively albeit they're still rather klunky in their implementation in Matlab vis a vis "real" statistics packages.