reading datenum instead of datestr from excel file

Question

I am trying to read a test table from an excel sheet , my excel sheet looks something like this

 Building      location start_date  start_time  end_date    end_time    number
 Test_Building  1.08    12.06.2016  19:00       12.06.2016      19:30    7

now when i read this as a table in matlab by using :

R = readtable('testtable.xlsx','ReadVariableNames',1)

I get something like

R = 

       Building        location     start_date     start_time      end_date      end_time    number
    _______________    ________    ____________    ____________    ____________    ________    ______

    'Test_Building'    1.08        '12.06.2016'    0.79167         '12.06.2016'    0.8125      7

Now if you notice the start_time and end_time are retrieved in datenum format instead of a datestrhow can I save this time such that when I retrieved these variables they are in datestr format ?

you need to specify in your call to `readtable` the 'Format' option and specify a %s (String) for start_time and end_time — GameOfThrows, Aug 15 '16 at 11:03
althought `Format` property is there is in the `readtable` but it is giving me an error `Invalid parameter name: Format.` — Novice_Developer, Aug 15 '16 at 11:11
can you show the line of code you used to call the readtable function with the Format option. — GameOfThrows, Aug 15 '16 at 12:31
Yes sure ! `R = readtable('testtable.xlsx','Format','%s%.2f%s%s%s%s%d','ReadVariableNames',1)` — Novice_Developer, Aug 15 '16 at 12:48
hmmm, it is possible that your matlab version does not have the Format option for readtable. — GameOfThrows, Aug 15 '16 at 12:51
Thanks for the help , but I have sort of solved the problem by add a `'` before i write my date and now I can read it through `datevec` — Novice_Developer, Aug 15 '16 at 12:53
@Umar In general you would want to feed the date number into datevec. — Dennis Jaheruddin, Aug 16 '16 at 09:37

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

The key thing to realize here is that if you use a date in Excel, it will not be stored as a string, but as the corresponding datenum. In particular, two people who open the same file may see a different string based on their settings.

As such there is no such thing as 'getting the date string from excel'.

There are workarounds for this, for instance by storing your date in excel as a string (as you mentioned in the comments). However, this is not always possible.

Perhaps there is even a possibility to load the string as Excel would display it given your current settings, but this is of course not very portable, and if you change your settings you could easily get in trouble because the following calculations in Matlab will not match the format anymore. Not to mention that you would need to parse date columns with different formats individually, even if they are in the same file.

As such, I recommend the following:

Read the datenum, and parse it in MATLAB

Of course you may want to a date, but even if you parse it to a string this would still be the recommended general solution.

You mention in the comments that you want to use datevec, therefore you should be able to use one of these two options:

DateVector = datevec(DateNumber)

Depending on your source you may need to add or substract a constant from the number to get the right date. (Probably this is not needed for Excel, but just try it once.)

Thankyou so much for the detailed reply , your solution works! The reason I couldnt use `datevec` directly was that these excel plots were being retrieved by a function which read this time as a `datetime(TMP.start_time,'InputFormat','HH:mm:ss')`where you can see the input format was `HH:mm:ss` , I didnt wanted to change the function as it was working for other excel files also ... Thanks again for the help — Novice_Developer, Aug 16 '16 at 10:09

reading datenum instead of datestr from excel file

1 Answers1

Read the datenum, and parse it in MATLAB