x2mdate

Excel serial date number to MATLAB serial date number or datetime format

Syntax

MATLABDate = x2mdate(ExcelDateNumber,Convention)

MATLABDate = x2mdate(___,outputType)

Description

MATLABDate = x2mdate(ExcelDateNumber,Convention) converts Excel^® serial date numbers to MATLAB^® serial date numbers or datetime format.

MATLAB date numbers start with 1 = January 1, 0000 A.D., hence there is a difference of 693960 relative to the 1900 date system, or 695422 relative to the 1904 date system. This function is useful with Spreadsheet Link™ software.

MATLABDate = x2mdate(___,outputType) converts Excel serial date numbers to MATLAB serial date numbers or datetime format using an optional input argument for outputType.

The type of output is determined by an optional outputType input. If outputType is 'datenum', then MATLABDate is a serial date number. If outputType is 'datetime', then MATLABDate is a datetime array. By default, outputType is 'datenum'.

Examples

Convert Excel Serial Date Numbers to MATLAB Dates

Given Excel® date numbers in the 1904 system, convert them to MATLAB® serial date numbers, and then to date character vectors.

ExDates = [35423  35788  36153];
MATLABDate = x2mdate(ExDates, 1)

MATLABDate = 1×3

      730845      731210      731575

datestr(MATLABDate)

ans = 3x11 char array
    '25-Dec-2000'
    '25-Dec-2001'
    '25-Dec-2002'

Alternatively, use the optional input outputType to specify 'datetime' to return datetime format.

ExDates = [35423  35788  36153];
MATLABDate = x2mdate(ExDates, 1,'datetime')

MATLABDate = 1x3 datetime
   25-Dec-2000   25-Dec-2001   25-Dec-2002

Input Arguments

ExcelDateNumber — Excel serial date number

serial date number

Excel serial date number, specified as a scalar or vector of Excel serial date numbers.

Data Types: double

Convention — Flag for Excel date system

0 (Excel 1900 date system is in effect) (default) | numeric with value 0 or 1

Flag for Excel date system, specified as a scalar or vector as a numeric with a value 0 or 1. Convention must be either a scalar or else must be the same size as ExcelDateNumber.

When Convention = 0 (default), the Excel 1900 date system is in effect. When Convention = 1, the Excel 1904 date system in used.

In the Excel 1900 date system, the Excel serial date number 1 corresponds to January 1, 1900 A.D. In the Excel 1904 date system, date number 0 is January 1, 1904 A.D.

Due to a software limitation in Excel software, the year 1900 is considered a leap year. As a result, all DATEVALUE's reported by Excel software between Jan. 1, 1900 and Feb. 28, 1900 (inclusive) differs from the values reported by 1. For example:

• In Excel software, Jan. 1, 1900 = 1

• In MATLAB, Jan. 1, 1900 – 693960 (for 1900 date system) = 2

  datenum('Jan 1, 1900') - 693960

  ans =
  
       2

Data Types: logical

outputType — Output date format

'datenum' (default) | character vector with values 'datenum' or 'datetime'

Output date format, specified as a character vector with values 'datenum' or 'datetime'. The output MATLABDate is in serial date format if 'datenum' is specified or datetime format if 'datetime' is specified. By default the output is in serial date format.

Data Types: char

Output Arguments

MATLABDate — MATLAB date

serial date numbers | datetime format

MATLAB date, returned as serial date numbers or datetime format.

The type of output is determined by an optional outputType input argument. If outputType is 'datenum', then MATLABDate is a serial date number. If outputType is 'datetime', then MATLABDate is a datetime array. By default, outputType is 'datenum'.