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

Question

Andre 2023-7-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1997263-how-can-i-get-excel-to-use-the-time-vector-of-a-timetable-properly

评论： Jeremy Hughes 2023-7-18

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é :)

2 个评论
显示无隐藏无

Walter Roberson 2023-7-18

I suspect you mean writetimetable

Jeremy Hughes 2023-7-18

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 2023-7-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1997263-how-can-i-get-excel-to-use-the-time-vector-of-a-timetable-properly#answer_1274783

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!

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

Answer 2

Shishir Reddy 2023-7-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1997263-how-can-i-get-excel-to-use-the-time-vector-of-a-timetable-properly#answer_1274793

编辑：Shishir Reddy 2023-7-18

在 MATLAB Online 中打开

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.