How to remove the H:M:S in datetime variables when "writetable" to Excel

Question

Jiashun Wu 2021-7-12

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/876648-how-to-remove-the-h-m-s-in-datetime-variables-when-writetable-to-excel

评论： Jiashun Wu 2021-7-12

Hi! I find a problem in my datetime variables when writing my table results into Excel via "writetable".

I would like to keep the D/M/Y format (e.g. 15/03/2008) in the table, but I finally find that the Excel file shows the D/M/Y H:M:S format (e.g. 15/03/2008 12:00:00 AM).

I am wondering how can I fix this problem --- so that the Excel file can show the original datetime format (without H:M:S) as in MATLAB.

Thanks a lot for your help!

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

请先登录，再进行评论。

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

Answer 1

Vinesh Katewa 2021-7-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/876648-how-to-remove-the-h-m-s-in-datetime-variables-when-writetable-to-excel#answer_744863

在 MATLAB Online 中打开

Hi Jiashun Wu,

I understand that you want the output in D/M/Y format and not with the H:M:S which is the default output of the function. To get the output of datetime function in a specific format, you can use the 'Format' name value pair with datetime function as follows:

>> T = datetime();
>> D = datetime(T, 'Format', 'dd/MM/yyyy');

Hope this helps.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Vinesh Katewa 2021-7-12

在 MATLAB Online 中打开

Dear Jiashun,

I replicated your steps and found out that the output in excel is in desired format while using csv format but otherwise while using xlsx format.

For example, for a table defined as below:

t = datetime();
t = datetime(year(t), month(t), day(t), 'Format', 'dd/MM/yyyy');
T = table([t; t; t]);

Following code outputs desired results:

writetable(T, 'mydata.csv', 'Delimiter', ',')

But the following gives the '12:00:00 AM' output with the time

writetable(T, 'mydata.xlsx', 'WriteMode', 'replacefile')

This is happening due to the default date and time formatting in xlsx files in Microsoft excel.

Please note that using different programs instead of excel I am able to see the date time in desired format in both extensions.

In conclusion, you can either change the default date time format in excel for your datetime column or change the file extension.

Hope this helps to resolve your query.

Jiashun Wu 2021-7-12

Dear Vinesh,

Thanks for this helpful comment!

Best regards,

Jiashun

请先登录，再进行评论。

How to remove the H:M:S in datetime variables when "writetable" to Excel

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

采纳的回答

3 个评论
显示 1更早的评论隐藏 1更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

How to remove the H:M:S in datetime variables when "writetable" to Excel

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

采纳的回答

3 个评论 显示 1更早的评论隐藏 1更早的评论

更多回答（0 个）

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

WeChat

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

3 个评论
显示 1更早的评论隐藏 1更早的评论