how can i get excel to use the time vector of a timetable properly ?

hi :)

i am having a script, where i automatically create timetables, cause they are so awesome and useful and after that i create an xlsx with writetime(). no i stumbled across a problem which popped into my eyes after having made a few measurements, cause the tme between each data point were 1 second, i didnt noticed that excel cant even read the time vector.

so right now excel cant make use of it, it looks like i have to do it all over again becase of that. i were searcing for an addon in excel, but didnt found one.

I already tried to get rid of the "sec" in the saved timetable, but that didnt workeds out either and is beside the point.

best regards

André :)

[Walter Roberson]

I suspect you mean writetimetable

[Jeremy Hughes]

Some example data and code would help illustrate the problem better.

There are a couple of answers which recommend using datenum, but I wouldn't suggest that as Excel will not know those numbers should be dates, and will need to be manually updated to recognize them.

Answer 1

[Menika]

Hi,

To resolve this, you can convert the time vector to a format that Excel can recognize, such as a serial date number or a formatted string.

As serial date number:

timeSerial = datenum(filename.Time);

writematrix(timeSerial, 'data.xlsx', 'Sheet', 1);

As formatted string:

timeString = datestr(filename.Time, 'yyyy-mm-dd HH:MM:SS');

writematrix(timeString, 'data.xlsx', 'Sheet', 1);

Hope it helps!

Answer 2

[Shishir Reddy]

Hi Andre,

As per my undertsanding, you are encountering an issue where you are creating timetables in MATLAB and saving them to an Excel file using the `writetime()` function. However, when you open the Excel file, the time vector is not properly readable or usable in Excel.

One possible solution to this problem is to convert the time vector to a format that Excel can recognize and display correctly. Here's a suggested approach:

1. Convert the time vector to a serial date number format in MATLAB. Excel can interpret and display serial date numbers correctly. The `datenum()` function in MATLAB can be used for this conversion. For example:

% Assuming your time vector is stored in a variable called 'time'
serialDateNum = datenum(time);

2. Save the converted serial date number vector to the Excel file using the `writematrix()` function instead of `writetime()`. Make sure to save the serial date number vector in the appropriate column or location in the Excel file. For example:

% Assuming you have the serial date number vector stored in a variable called 'serialDateNum'
writematrix(serialDateNum, 'output.xlsx', 'Sheet', 'Sheet1', 'Range', 'A1');

By converting the time vector to a serial date number format before saving it to the Excel file, you should be able to open the file in Excel and have the time values properly displayed and usable.

For further reference, kindly refer this link to know more about ‘datenum’ and 'writematrix'

https://in.mathworks.com/help/matlab/ref/datenum.html

https://in.mathworks.com/help/matlab/ref/writematrix.html

If you encounter any further issues or have specific requirements for the time format in Excel, please provide more details, and I'll be happy to assist you further.

I hope this helps resolving the issue.