xlsread problem with datenum

Hi all,

When importing an xls file made of two columns: the first of timestamp and the second of value, I have an error doing the datenum on the timestamp cell. Indeed, the problem is how xlsread import the excel dates. In the last value you can see that the excel date '11/1/2015 00:00:00' is truncated and thus datenum do not find the correct format. Do you know how I can convert in a fast and easy way this format?

    '10/31/2015 11:59:29 PM'            [159510]
    '10/31/2015 11:59:34 PM'            [159527]
    '10/31/2015 11:59:42 PM'            [159528]
    '10/31/2015 11:59:43 PM'            [159536]
    '11/1/2015'                         [159537]

Thanks a lot

[Jan]

If you post the relevant part of the code, suggesting a modification is easier and more likely to match your needs.

Answer 1

[Jan]

You can append the trailing time string manually:

C = {'10/31/2015 11:59:29 PM'; ...
     '10/31/2015 11:59:34 PM'; ...
     '10/31/2015 11:59:42 PM'; ...
     '10/31/2015 11:59:43 PM'; ...
     '11/1/2015'};
Cropped = cellfun('length', C) < 10;
C(Cropped) = strcat(C(Cropped), {' 00:00:00 AM'});

[Sal]

Thanks a lot Jan,

I was looking at the function generated by the wizard and actually a solution seems to be the handle that xlsread uses. In particular:

    [~, ~, raw, dates] = xlsread(workbookFile, sheetName, sprintf('A%d:B%d',startRow(1),endRow(1)),'' , @convertSpreadsheetExcelDates);

The function @convertSpreadsheetExcelDates seems to do this but I would like to not use the startRow and endRows because I do not know them before since all the files are different.

What do you think?

Thanks a lot