Documentation Center

  • Trial Software
  • Product Updates

When to Convert Dates from Excel Files

MATLAB and Excel Dates

Both MATLAB® and Excel® applications can represent dates as character strings or numeric values. For example, May 31, 2009, can be represented as the character string '05/31/09' or as the numeric value 733924. Within MATLAB, the datestr and datenum functions allow you to easily convert between string and numeric representations.

You must convert dates when:

  • Importing any Excel file on a system without Excel for Windows®

  • Importing a spreadsheet with dates stored as numbers using xlsread or importdata

  • Exporting an array or table with dates stored as numbers

You do not need to convert dates when:

  • Importing a spreadsheet using the Import Tool

  • Importing a spreadsheet with dates stored as strings on a system with Excel for Windows

  • Exporting an array or table with dates stored as strings

Both Excel and MATLAB represent numeric dates as a number of serial days elapsed from a specific reference date, but the applications use different reference dates.

The following table lists the reference dates for MATLAB and Excel. For more information on the 1900 and 1904 date systems, see the Excel help.

ApplicationReference Date
MATLABJanuary 0, 0000
Excel for WindowsJanuary 1, 1900
Excel for the MacintoshJanuary 2, 1904

Import an Excel File with Numeric Dates

Consider the hypothetical file weight_log.xls with

Date       Weight
10/31/96   174.8
11/29/96   179.3
12/30/96   190.4
01/31/97   185.7

To import this file, first convert the dates within Excel to a numeric format. In Windows, the file now appears as

Date       Weight
35369      174.8
35398      175.3
35429      190.4
35461      185.7

Import the file:

wt = xlsread('weight_log.xls');   

Convert the dates to the MATLAB reference date. If the file uses the 1900 date system (the default in Excel for Windows):

datecol = 1;
wt(:,datecol) = wt(:,datecol) + datenum('30-Dec-1899');

If the file uses the 1904 date system (the default in Excel for the Macintosh):

datecol = 1;
wt(:,datecol) = wt(:,datecol) + datenum('01-Jan-1904');

Export to an Excel File with Numeric Dates

Consider a numeric matrix wt_log. The first column contains numeric dates, and the second column contains weights:

wt_log = [729698 174.8; ...
          729726 175.3; ...
          729760 190.4; ...
          729787 185.7];

% To view the dates before exporting, call datestr:
datestr(wt_log(:,1))

The formatted dates returned by datestr are:

04-Nov-1997
02-Dec-1997
05-Jan-1998
01-Feb-1998

To export the numeric matrix to Excel for Windows (and use the default 1900 date system), convert the dates:

datecol = 1;
wt_log(:,datecol) = wt_log(:,datecol) - datenum('30-Dec-1899');
xlswrite('new_log.xls', wt_log); 

To export for use in Excel for the Macintosh (with the default 1904 date system), convert as follows:

datecol = 1;
wt_log(:,datecol) = wt_log(:,datecol) - datenum('01-Jan-1904');
xlswrite('new_log.xls', wt_log); 
Was this topic helpful?