How to specify format when writing to Excel?

I'm writing a big cell array to an Excel sheet, but two of my columns need work. Both my Date and Time columns are not being recognized as dates and times, so my filters are just match-based (instead of organizing from year > month > day > etc. it just lets you choose singular days).

If my cell array consists of data formatted as '02/27/2021 15:18:34' how do I make Excel display the date as: 02/27/2021 and the time as: 15:18:34? Both columns get the same data written into Excel, but the cells should display either the date or time.

The following lines don't do anything useful:

xlsWS.Columns.Item(2).NumberFormat = 'mm/dd/yyyy'; 
xlsWS.Columns.Item(3).NumberFormat = 'h:mm:ss;@'; 

This is what the Excel should look like:

I don't have much VBA experience. I appreciate any suggestions!

Thank you!

Bobby

Answer 1

[Image Analyst]

You can either make up the Excel workbook in advance and format it exactly as you want it, then have MATLAB write into it.

Or you can get an ActiveX server and manually specify formatting, like the attached demo code shows.

[Bobby]

Thanks for the reply. I don't think I understand your suggestion. I'm currently using writecell() and then using actxserver() to manipulate the Excel. Are you saying if I don't use writecell() until the end, and I change the format of my two columns, my NumberFormat lines will work?

I'm fairly new to using COM so I have trouble understanding some of this.

[Image Analyst]

You can use writecell(). Starting with a recent version (not sure which) it will retain the formatting you've set up in the existing workbook. Before that it used to blow away all your custom formatting.

You can either use writecell() or not. If you don't you can write stuff in with the ActiveX commands instead. Regardless if you use ActiveX before or after writecell(), it should maintain your formatting as long as you save your ActiveX changes to the file.

I'm sure you'll be able to learn ActiveX commands. If you ever need to do something new, not in my class, just go into Excel and record a macro and then see what code it recorded and try to transfer that to MATLAB.