Create a numeric variable DATE+HOUR

Hi. I hope you're ok.

I write this question because I want to create a new numeric variable which contains the date (i.e. 12/09/2019) and the hour (i.e. 01:15:20). I did this in Excel and the result is given in the following photo:

However, If I try to do this in MATLAB (R19b) the result is quite different.

Can you tell me if I'm doing something wrong? I don't know if the sintaxis of the date and hour are correct, and if not, what's wrong with that (I want the result given in my Excel sheet).

Thanks!

David

Answer 1

[Adam Danz]

There's rarely a good reason to use datenum rather than datetime values. See Why are datetime arrays better than datenum?

% Convert to datetime
Time = '01:15:02 12/09/19';
datetime(Time,'InputFormat','HH:mm:ss MM/dd/yy')

ans = datetime
   09-Dec-2019 01:15:02

Date numbers in Excel represent the number of days since Jan 1 1900 whereas date numbers in Matlab represent the number of days since Jan 1,0000 so there's a 693960 day difference (see documentation).

If you have the Financial Toolbox you could use x2mdate / m2xdate or you could convert it on your own using the offset mentioned in the documentation.

[David E.S.]

Great explanation, thanks for your help!

David

[Steven Lord]

FYI if you were importing a date number from Microsoft Excel, rather than using the text representation of the date and time you could use datetime with the 'ConvertFrom' option.

n = 43720.05231

n = 4.3720e+04

datetime(n, 'ConvertFrom', 'excel')

ans = datetime
   12-Sep-2019 01:15:19

[David E.S.]

Is it possible to do that conversion from date to number, using the 'ConvertForm', 'excel'?

Thanks!

[Adam Danz]

Sure use either of the following syntaxes

e = exceltime(t)

e = exceltime(t,dateType)

Excel supports two date systems that start from 1900 or 1904 which is reason for the second argument.

https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487