Documentation

This is machine translation

Translated by
Mouseover text to see original. Click the button below to return to the English verison of the page.

Import and Export Dates to Excel Files

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.

ApplicationReference Date
MATLAB serial date numberJanuary 0, 0000
Excel for WindowsJanuary 1, 1900
Excel for the MacintoshJanuary 2, 1904

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

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')`