Datetimes in table not displayed

3 views (last 30 days)
Dennis
Dennis on 12 Jul 2023
Commented: Peter Perkins on 17 Jul 2023
Hi, I'm currently trying to finish my master thesis over the summer but I'm running into some problems with processing data in Matlab. I have a table with 401 rows of different measures of datetimes:
I want to export it to Excel with all datetimes, but the datetimes are stored in one cell, and can only be seen when clicking on the cell:
I want all the datetimes of each cell being shown in the original table on their respective row, so I can copy all the data all at once to Excel (instead of manually doing it for each row, 401 times). With what code can I change to table so that all datetimes are visible at once? Hope someone knows this, it would help me so much :).
  4 Comments
Show 2 older comments
Dennis
Dennis on 12 Jul 2023
Once again thank you for the quick reply :). Timezones do not have to be converted, it is all about the relative time.
Dennis
Dennis on 12 Jul 2023
@Stephen23 Yes I am aware that some cells do not contain daytime objects, these can be left blank. What do you mean with buggy data?

Sign in to comment.

Sign in to answer this question.

Accepted Answer

Stephen23
Stephen23 on 12 Jul 2023
Edited: Stephen23 on 12 Jul 2023
Ran in:
C = {datetime(2021,1,1:2);datetime(2022,2,3:6);datetime(2023,3,7:9)}
C = 3×1 cell array
{[01-Jan-2021 02-Jan-2021 ]} {[03-Feb-2022 04-Feb-2022 05-Feb-2022 06-Feb-2022]} {[07-Mar-2023 08-Mar-2023 09-Mar-2023 ]}
D = NaT(numel(C),0);
for k = 1:numel(C)
V = C{k};
D(k,1:numel(V)) = V;
end
display(D) % optional
D = 3×4 datetime array
01-Jan-2021 02-Jan-2021 NaT NaT 03-Feb-2022 04-Feb-2022 05-Feb-2022 06-Feb-2022 07-Mar-2023 08-Mar-2023 09-Mar-2023 NaT
writematrix(D,'test.xlsx') % much better than copy-and-paste
  3 Comments
Show 1 older comment
Dennis
Dennis on 12 Jul 2023
@Stephen23 You are absolutely amazing! I'm so happy with your help, you really saved me so much stress and effort. Thank you :)
Peter Perkins
Peter Perkins on 17 Jul 2023
These are all tagged with the same Europe/Amsterdam time zone, so as long as you keep in mind that once they get to Excel, you need to interpret them as "local time" you're OK. But the day you start having different time zones, you are in trouble because Excel knows nothing about time zones. You;d want to convert all to the same tz.
The NaT trick is a good one, and that may be what you want, but you might also consider writing a loop to export one cell at a time to Excel. I forget what NaT ends up as in Excel, but if you only write the data you have, you will have empty cells where you don't have data, which may be better.

Sign in to comment.

More Answers (1)

Malay Agarwal
Malay Agarwal on 12 Jul 2023
Hi @Dennis,
I am assuming that your datetimes are in a 401x1 cell array, where each row in the cell array is a vector of datetimes. Please use the following code:
% Set the number of rows
numRows = 401;
% Initialize the cell array
randomDates = cell(numRows, 1);
% Generate random datetime values for each row
for i = 1:numRows
% Generate a random number of datetime values per row
numValues = randi([1, 10]); % Modify the range as per your requirement
% Generate random datetime values for the current row
randomDates{i} = datetime(randi([2000, 2021], numValues, 1), ... % Year
randi([1, 12], numValues, 1), ... % Month
randi([1, 28], numValues, 1), ... % Day
randi([0, 23], numValues, 1), ... % Hour
randi([0, 59], numValues, 1), ... % Minute
randi([0, 59], numValues, 1)); % Second
end
% Determine the maximum length of the datetime vectors
maxVectorLength = max(cellfun(@numel, randomDates));
% Create a NaN-filled matrix with the maximum length
dataMatrix = NaT(numRows, maxVectorLength);
% Populate the matrix with the datetime values
for i = 1:numRows
dataMatrix(i, 1:numel(randomDates{i})) = randomDates{i}';
end
% Write the matrix to an Excel file
filename = 'data.xlsx';
writematrix(dataMatrix, filename);
The exported file is attached to the answer.
  4 Comments
Show 2 older comments
Stephen23
Stephen23 on 12 Jul 2023
Edited: Stephen23 on 12 Jul 2023
"How would I fill out the table with the already existing datetimes? "
You use your data, not the random dates.
We cannot test code on imaginary data, we need actual data to test code on. Because you did not upload any sample data, we had to invent some random dates ourselves. Of course you do not use our random dates, you use your data.
If you want us to test our code on your data, then please supply us with your data.
Dennis
Dennis on 12 Jul 2023
Thank you for the fast reply. My data can be found in the attachment.

Sign in to comment.

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Tags

Products


Release

R2023a

Community Treasure Hunt

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

Start Hunting!