How to write a timetable to excel with rowtimes as dates without times?

Take this simple example:

m = (1:3)';
dates = datetime(2025,m,15);
tt = timetable(dates,m);
writetimetable(tt,'tt.xlsx')

tt is a 3x1 timetable with dates but no times 00:00:

dates m

___________ _

15-Jan-2025 1

15-Feb-2025 2

15-Mar-2025 3

But the resulting excel sheet tt.xlsx includes the times 00:00:

dates m

1/15/25 00:00 1

2/15/25 00:00 2

3/15/25 00:00 3

How can I make writetimetable create an excel sheet with dates but no times 00:00?

Answer 1

[Star Strider]

That is likely a problem with Excel.

MATLAB writes the timetable correctly --

m = (1:3)';
dates = datetime(2025,m,15)

dates = 3×1 datetime array
   15-Jan-2025
   15-Feb-2025
   15-Mar-2025

tt = timetable(dates,m);
writetimetable(tt,'tt.xlsx')

TT1 = readtimetable('tt.xlsx')

TT1 = 3×1 timetable
       dates       m
    ___________    _

    15-Jan-2025    1
    15-Feb-2025    2
    15-Mar-2025    3

(I am using Ubuntu 24.04 so I do not have Excel or access to it.)

.

[Dyuman Joshi]

"That is likely a problem with Excel. "

I doubt that, because the Excel file is just storing the data that was written by MATLAB.

I believe that the "issue" is with datetime() particularly how it defines dates and times. Using writetable() also leads to the same issue.

[Star Strider]

Excel seems to be adding the times on its own. It may be necessary to interact with Excel to correct that, since it does not appear to be a MATLAB problem.

The timetable that MATLAB writes appears to be as desired.

[Dyuman Joshi]

The times data is present with the datetime() object, it's just not explicitly shown.

However the same issue doesn't appear with text files -

m = (1:3)';
dates = datetime(2025,m,15);
tt = timetable(dates,m);
writetimetable(tt,'tt.txt')

type tt.txt

dates,m
15-Jan-2025,1
15-Feb-2025,2
15-Mar-2025,3

This makes me suspect that the issue is how writetimetable() processes writing data with these formats.

[Walter Roberson]

@Dyuman Joshi

writetimetable() writes all dates in Excel date format, which is seconds since January 1, 1900. writetimetable() does not have the ability to customize the display of date cells.

[Dyuman Joshi]

Interesting - I knew about the datum but didn't know about the unit being seconds.

So it seems - "Excel uses a floating-point number to store dates and times. The number 1.0 represents the first second of January 1, 1900, in the 1900 Date System" (source - https://en.wikipedia.org/wiki/Year_1900_problem#Microsoft_Excel)

It would been better if writetimetable() (and writetable() as well) did have the ability to customize display of datetime() objects to be written in Excel.

As it stands - OP will, most likely, have to modify the data after writing it.

[Lars Svensson]

Thanks for the answers/comments!

If there was a way in Matlab to change the format in the first column in the excel file from "m/d/yy hh:mm" to "d-mmm-yyyy", my problem would be solved. Is there a way to do this? (Also, I am using Matlab on a Mac.)

[Star Strider]

I am not certain how Excel deals with date formats (it might be necessary to do all that in Excel), however specifying a format in MATLAB is straightforward --

m = (1:3)';
dates = datetime(2025,m,15)

dates = 3×1 datetime array
   15-Jan-2025
   15-Feb-2025
   15-Mar-2025

dates.Format = "dd-MMM-yyyy"

dates = 3×1 datetime array
   15-Jan-2025
   15-Feb-2025
   15-Mar-2025

tt = timetable(dates,m);
writetimetable(tt,'tt.xlsx')

TT1 = readtimetable('tt.xlsx')

TT1 = 3×1 timetable
       dates       m
    ___________    _

    15-Jan-2025    1
    15-Feb-2025    2
    15-Mar-2025    3

Here, that is the same as the original format.

.

[Lars Svensson]

As Joshi Dyuman says in the reply to Chuguang Pan below, one has to open the excel file to see whether there are times there or not.

I have noticed that if you write the timetable as an csv file and open the csv file in excel, there are no times there:

m = (1:3)';
dates = datetime(2025,m,15);
tt = timetable(dates,m)
writetimetable(tt,'tt.xlsx')    % Undesired times in dates column in excel file
writetimetable(tt,'ttcsv.csv')  % No times in dates column if opened in excel

But it would be good to find a way in Matlab in Matlab to change the format in the first column in the excel file from "m/d/yy hh:mm" to "d-mmm-yyyy". Then the problem would be solved. Is there a way to do this? (Also, I am using Matlab on a Mac.)

[Star Strider]

Unless you are doing calculations in Excel (it is always best to do the calculations in MATLAB instead), and since .csv or other text-based files preserve the fomatting you want, I would just use .csv files.

I am not sure how Excel works on Mac computers. I am using Ubuntu Linux 24.04, and I beleive everything in MathWorks (including MATLAB Online and the code execution here) uses a version of Linux as well.

I would just use .csv files and be done with it, unless there is some specific reason to use Excel in your situation. I cannot imagine any situation in which Excel would be preferable to MATLAB for calculations, plotting, and data formatting.

[Lars Svensson]

Dear Star Strider, I agree completely that Matlab is definitely preferable to Excel for calculations, plotting, and data formatting. I think Excel is convenient, though, for simple inspection of data and for data transfers with other researchers that don't use Matlab.

In this case, the excel files are only used for data storage, and I can certainly use csv files (or mat files) or just accept the inclusion of 00:00, since there doesn'r seem to be a simple solution or workaround (other than the csv).

Thank you for your consideration and advice.

[Star Strider]

As always, my pleasure!

If you are using them only in MATLAB, and do not need to use them in any other application, or share them with anyone not having access to MATLAB, consider saving them (save and load) to .mat files.

[Jeremy Hughes]

I'll confirm that the format that Excel is applying here is out of MATLAB's control. The PreserveFormat argument should work on a Mac, but has no effect on the date format, unfortunately.

Answer 2

[Chuguang Pan]

You can use "InputFormat" option to specify the date format

m = (1:3).';
dates = datetime(2025,m,15,"InputFormat","dd-MM-yyyy");
tt = timetable(dates,m);
writetimetable(tt,'TT.xlsx');
readtimetable('TT.xlsx')

ans = 3×1 timetable
       dates       m
    ___________    _

    15-Jan-2025    1
    15-Feb-2025    2
    15-Mar-2025    3

[Dyuman Joshi]

This does not work. The problem is with the excel that saves the data - Open the excel and you'd find the issue OP is facing.

Answer 3

[Paul]

"If there was a way in Matlab to change the format in the first column in the excel file from "m/d/yy hh:mm" to "d-mmm-yyyy", my problem would be solved."

Seems like you might be able to define an empty .xlsx file that has the first column, starting from the second row, have the format you want. Call it empty.xlsx. Then when you want to write, copyfile empty.xlsx to the filename you want, and then use writetimetable with PreserveFormat and UseExcel both set to true. I didn't test this approach.

[Lars Svensson]

@Paul

I have tested this approach previously and it didn't work for me.

[Paul]

Interesting. Works for me. Windows 11. Matlab 2024a.

>> copyfile empty.xlsx tt.xlsx
>> m = (1:3)';
>> dates = datetime(2025,m,15);
>> tt = timetable(dates,m);
>> writetimetable(tt,'tt.xlsx','PreserveFormat',true,'UseExcel',true);

[Lars Svensson]

Many thanks, Paul. I am afraid that I not read your answer thoroughly enough and missed the main point, the use of PreserveFormat and UseExcel .

However, when I try your suggestion on my Mac, it still does not work and I get the following error message

"Warning: Using Excel for reading and writing spreadsheet files is only supported on Windows. Switching 'UseExcel' to false."

So it works on Windows but not om Mac.

Answer 4

[Walter Roberson]

For MacOS and Linux, it is not possible to PreserveFormat .

About the best you can do on MacOS is to convert the timetable to a table, set the Format property of the appropriate column of the table to something like 'dd-MMM-uuuu', then set the appropriate column to be string() of the appropriate column. This will convert the column to the text in dd-MMM-uuuu format.

Unfortunately it is likely that Excel will then interpret the column as text rater than as datetime format.

[Paul]

The documentation does not say that PreserveFormat is applicable only to Windows. Should it?

[Stephen23]

"The documentation does not say that PreserveFormat is applicable only to Windows. Should it?"

Yes

[Paul]

According to @Jeremy Hughes in this comment, PreserveFormat should work on Mac, but perhaps with no capability to format dates.