converting excel datetimes in matlab

I am wanting to calculate the mean temperature (and standard deviation) for each day in 'seasim_temp', when I do this there are missing datetimes in 'unique_dates' and so missing data when I plot this (see missing_data.fig). I believe there is a problem with the format of the datetimes going from excel to matlab. See excel_figure to see what the data actually looks like in excel.

It would be great to understand more about formatting datetimes as I often come across a problem with them!

[Gyan Vaibhav]

Hi Sophia,

In the excel file there are timestamps such as 58:19.0, can you explain the format, and how are you linking these timestamps to specific dates?

[Stephen23]

"I believe there is a problem with the format of the datetimes going from excel to matlab."

Like almost every other question on this forum related to Excel dates, there are problems with the Excel worksheet itself.

Column A actually contains a mixture of cells formatted as general/date and as number. The first of multiple format changes occurs between line 758 (general/date) and line 759 (number):

But the format change is not the cause of your problems, just a hint of them. At that point the dates stop being stored in the XML as serial date numbers (i.e. proper Excel dates) and start being stored as a shared string (i.e. text):

With each format change the saved data type also changes. Ugh. In any case, lets go and have a look at the shared string XML and see the first text "date" in all of its glory:

So what you perhaps think is a nice column of dates is in fact a confusion of Excel dates and text. Perhaps you expect READTABLE to automagically import data using the philosophy import what I want, not what I give you. Users often do.

The best solution would be to fix the dates in that spreadsheet so that they are actually stored as dates and not just as text that look like dates because they have some pretty numbers in them. Looking like a date is not the same thing as being a date.