Documentation

MATLAB and Excel Dates

Microsoft^® Excel^® software can represent dates as text or numeric values. For example, in Excel for Windows^®, you can express April 1, 2012 as the character vector '04/01/12' or as the numeric value 41000. The best way to represent dates in MATLAB^® is to use datetime values. However, MATLAB functions import dates from Excel files as text or numeric values. Additionally, you might want to export text or numeric dates generated by existing code. Use the datetime function to convert text representing dates and serial date numbers to datetime values.

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.

This table lists the default reference dates for MATLAB and Excel. For more information about default reference dates in Excel, see the Excel help.

Import Dates on Systems with Excel for Windows

This example shows how to import an Excel file containing dates into a MATLAB table on a system with Excel for Windows.

Create the hypothetical file weight.xls that contains the following data.

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

Import the data using readtable.

T = readtable('weight.xls')

T = 

        Date        Weight
    ____________    ______

    '10/31/1996'    174.8 
    '11/29/1996'    179.3 
    '12/30/1996'    190.4 
    '1/31/1997'     185.7 

On systems with Excel for Windows, the Date variable of the output table is a cell array of character vectors representing the dates.

Convert the text representing dates in T to datetime values, using the datetime function.

T.Date = datetime(T.Date,'InputFormat','MM/dd/yyyy')

T = 

       Date        Weight
    ___________    ______

    31-Oct-1996    174.8 
    29-Nov-1996    179.3 
    30-Dec-1996    190.4 
    31-Jan-1997    185.7 

Import Dates on Systems Without Excel for Windows

This example shows how to import an Excel file containing dates into a MATLAB table on a system without Excel for Windows.

Create the hypothetical file weight.xls that contains the following data.

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

Import the data using readtable.

T = readtable('weight.xls')

T = 

    Date     Weight
    _____    ______

    35369    174.8 
    35398    179.3 
    35429    190.4 
    35461    185.7 

The Date variable of the output table is an array of MATLAB serial date numbers.

Convert the numeric dates in T to MATLAB datetime values.

• If the file uses the 1900 date system (the default in Excel for Windows), type:

  T.Date = datetime(T.Date,'ConvertFrom','excel')

  T = 
  
              Date            Weight
      ____________________    ______
  
      31-Oct-1996 00:00:00    174.8 
      29-Nov-1996 00:00:00    179.3 
      30-Dec-1996 00:00:00    190.4 
      31-Jan-1997 00:00:00    185.7 

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

  T.Date = datetime(T.Date,'ConvertFrom','excel1904');

Excel date numbers are rounded to the nearest microsecond.

Export Dates to Excel File

• Export Datetime Values

• Convert Numeric Dates to Datetime Values Before Export

Export Datetime Values

This example shows how to export datetime values to an Excel file using the writetable function.

Create a row vector of datetime values.

d = datetime({'11/04/1997','12/02/1997','01/05/1998','02/01/1998'},...
    'InputFormat','MM/dd/yyyy');

Create a row vector of sample data.

weights = [174.8 179.3 190.4 185.7];

Use the table function to create a table with columns that contain the data in d and weights. Use the 'VariableNames' name-value pair argument to specify variable names in the table.

T = table(d',weights','VariableNames',{'Date','Weight'})

T = 

       Date        Weight
    ___________    ______

    04-Nov-1997    174.8 
    02-Dec-1997    179.3 
    05-Jan-1998    190.4 
    01-Feb-1998    185.7 

Export the table to a file named myfile.xls using the writetable function.

writetable(T,'myfile.xls');

The Excel file contains the dates represented as character vectors.

Convert Numeric Dates to Datetime Values Before Export

This example shows how to export convert numeric dates to datetime values before exporting to an Excel file using the writetable function.

Create a matrix that contains dates represented as numeric values in the first column.

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

Convert the matrix to a table. Use the 'VariableNames' name-value pair argument to specify variable names in the table.

T = array2table(wt,'VariableNames',{'Date','Weight'})

T = 

       Date       Weight
    __________    ______

     7.297e+05    174.8 
    7.2973e+05    175.3 
    7.2976e+05    190.4 
    7.2979e+05    185.7 

Convert the MATLAB serial date numbers in the Weight variable to datetime values.

T.Date = datetime(T.Date,'ConvertFrom','datenum')

T = 

            Date            Weight
    ____________________    ______

    04-Nov-1997 00:00:00    174.8 
    02-Dec-1997 00:00:00    175.3 
    05-Jan-1998 00:00:00    190.4 
    01-Feb-1998 00:00:00    185.7 

Export the data using the writetable function.

writetable(T,'myfile.xls')