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

8 次查看（过去 30 天）
Andre
Andre 2023-7-18
评论： 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 个评论
Jeremy Hughes
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.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

Menika
Menika 2023-7-18
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!
Shishir Reddy
Shishir Reddy 2023-7-18
编辑：Shishir Reddy 2023-7-18
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.

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by