When using Readtable, how do I convert column of data from text to date?

I'm a complete newbie. I have been able to download data from an excel file using readtable as my boss requested. However, the 1st column is a date (and is defined as such in excel), but comes through as text. I need this column to be converted from text to date format. I know this is easy, but I have been trying to find the answer all afternoon.

Thanks.

Answer 1

[Walter Roberson]

Drawing upon the discussion in http://www.mathworks.com/matlabcentral/answers/223442-table-group-by-datetime and http://www.mathworks.com/matlabcentral/answers/180087-using-the-new-datetime-structure-within-datasets

YourTable.Date = datetime(YourTable(1));

to create a new table field named Date that had the appropriate content.

Passing a 'Format' option would be a good idea to be sure the dates are interpreted correctly.

I am assuming here that the "text" is something like '2007/05/19 23:14' not something like '731045.83' which would also be a text representation of an Excel date. If what you received was a numeric (non-text) Excel date such as 731045.83 then you should use the datetime() option 'convertfrom', 'excel' as the numbers are not exactly the same as datenum values.

The first link I referenced indicates that depending on what you want to do with the dates, the newer datetime objects might not be your best choice in current implementations, in which case you might want to use datenum() instead of datetime(). (Note: the options for datenum() are different than for datetime())

[NJBadger]

Thank you for your help. I tried to implement your solution and got the following error message...

Error using datetime (line 617) Input data must be a numeric or a cell array or char matrix containing date/time strings.

Error in myvar (line 15)

final.date = datetime(final(:,1))

if this helps, my data looks like this (in matlab file)...

'1/1/2008' 9.30000000000000 47.3200000000000

'1/1/2009' 7.51000000000000 53.6700000000000

'1/1/2010' 7.63000000000000 47

One last question...I searched quite hard for the right answer, how should I have known to look where you found the correct answer?

[NJBadger]

oops sorry. it was actually...

final.date = datetime(final(1))

the code above was my failed attempt to correct the issue

[Walter Roberson]

Try

final.date = datetime(final{:,1}, 'format', 'MM/DD/YYYY');  %or DD/MM/YYYY as appropriate

I have not used tables much so I tend to forget the syntax for accessing the contents of columns :(

Answer 2

[Peter Perkins]

NJBadger, two suggestions:

1) If you use the Import Tool rather than readtable, you can read directly to a datetime variable in the table.

2) If the spreadsheet has column headers, and the first one is "Date", then readtable creates a table with a variable called Date, and to convert those strings to a datetime, do this:

final.Date = datetime(final.Date)

Walter's suggestion to use braces will work, but to access one table variable, using dot subscripting is simpler. Braces is good for multiple table variables.

This assumes you're doing this on Windows with Excel installed. If you're using Linux, readtable with return Excel date numbers, not strings, and you'll need to do something like

final.Date = datetime(final.Date,'ConvertFrom','Excel')

Hope this helps.

[Walter Roberson]

braces are useful if you did not have column headers and so need to work positionally rather than by column name.