How to a convert a date to a number and back again

Question

I have the date 1/11/2010

and use the function

= date(year(A1),month(A1),day(A1))

to convert the date to the number to 40189 with Excel.

Can I use MATLAB to convert the number 40189 back to the date again?

Answer 1

Use DATESTR

>> datestr(40189)
ans =
12-Jan-0110

Unfortunately, Excel starts counting at 1-Jan-1900. Find out how to convert serial dates from Matlab to Excel by using DATENUM

>> datenum(2010,1,11)
ans =
      734149
>> datenum(2010,1,11)-40189
ans =
      693960
>> datestr(40189+693960)
ans =
11-Jan-2010

In other words, to convert any serial Excel date, call

datestr(excelSerialDate + 693960)

EDIT

To get the date in mm/dd/yyyy format, call datestr with the specified format

excelSerialDate = 40189;
datestr(excelSerialDate + 693960,'mm/dd/yyyy')
ans =
01/11/2010

Also, if you want to get rid of the leading zero for the month, you can use REGEXPREP to fix things

excelSerialDate = 40189;
regexprep(datestr(excelSerialDate + 693960,'mm/dd/yyyy'),'^0','')
ans =
1/11/2010