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?
+2
A:
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
Jonas
2010-10-17 17:16:02
ok.. I will try that out :)
mtlb
2010-10-19 17:25:48
Note that some Excel files start their epoch at 1904, not 1900. (See http://support.microsoft.com/kb/180162) If you're writing generic Excel/Matlab date conversion code, you'll need to either test for or provide an input to indicate the 1904 special case. The datenum offset is 695422 for 1904 epoch Excel files.
Andrew Janke
2010-10-19 18:31:53
@Andrew Janke: Gah! Thanks a lot for pointing this out.
Jonas
2010-10-19 20:00:31