MATLAB: Xlsread function returns numeric date when it reads string date from a column

MATLAB

Why xlsread function returns numeric date when it reads string date from a column?

How can I read an Excel spread sheet that has "DEC 21" in a cell and not have it converted to a date?

Best Answer

The "xlsread" function exhibits this behavior because Excel is storing the information internally as a "datenum". You can use "datestr" function to convert the date to the correct format after import.

For example:

>> datestr('12/21/16','mmm-dd')

ans =

Dec-21

For further information regarding "datestr" please refer the documentation at:

MATLAB Command: doc datestr

Web Link: http://www.mathworks.com/help/releases/R2015b/matlab/ref/datestr.html

Related Solutions

MATLAB: Do “datenum” and “datestr” not give the same date after conversion

The issue happens because "MM" is the format for 'minute' in "datenum".

You can use the lower case "mm" to get the expected output:

>> a = datenum('20101231','YYYYmmDD');
>> b = datestr(a)
b =
      '31-Dec-2010'

MATLAB: How to keep the same time format when exporting the Table to Excel

The issue here is the use of "datetime" function to store time values. As the name of the function suggests, it takes both the date and time into account when it returns the output "time" in the question above. Since the input does not specify any date specifically, "datetime" function uses the current date as the date component of the output which explains why the data written to Excel is based on the day, the data is being written to Excel.

Since the input data corresponds to only time and not date, the correct function to use in this context is "duration" which represents elapsed times in units of fixed length, such as hours, minutes, and seconds. Using the "duration" function instead of "datetime" results in the data being written to Excel in the desired form:

>> time = duration({'03:05:12';'04:05:12';'05:05:12'},'Format','hh:mm:ss');
>> data = [1; 2; 3];
>> T = table(time,data);
>>
>> writetable(T, 'Book1.xlsx');

Here is the documentation for "duration" for your reference:

https://www.mathworks.com/help/matlab/ref/duration.html

Best Answer

Related Solutions

MATLAB: Do “datenum” and “datestr” not give the same date after conversion

MATLAB: How to keep the same time format when exporting the Table to Excel

Related Question