import date from matlab to ms excel in the form 'HH:MM PM' using xlswrite

this is my code. The problem is that in Ms excel the display for time is in the form of 22 34 12 34 54 65. there will be 6 numbers.

                    %%%System Date and Time %%%
                    ds=datestr (now, 'mmm dd, yyyy');
                    set(handles.date,'String',ds);
                    time=datestr (now, 'HH:MM PM');
                    set(handles.time,'String',time);
                    %%%%%DATA ACQUISITION %%%%%
                    LOAD1(i,:) = [LP1 LQ1 LV1 timex];
    header = {'P(Watts)','Q(var)','V(volts)', 'TIME'};
    %%DATA for LOAD 1
    xlswrite(day,header,'LOAD1','A1');
    xlswrite(day,LOAD1,'LOAD1','A2');

Answer 1

[Sven]

Hi Jovanie,

The trick is to make sure excel receives a date in a format that it recognises as a date, and then let excel handle any formatting. Try the following code:

dn = now;
dn2 = now - datenum('30-Dec-1899');
ds1 = datestr(now, 'mmm dd, yyyy');
ds2 = datestr(now, 'HH:MM PM');
ds3 = datestr(now, 'yyyy/mm/dd HH:MM PM'); 
dateSet = {dn; dn2; ds1; ds2; ds3};
xlswrite('dateTest.xlsx',dateSet,'Sheet1','A1');

Now open the file in excel. You'll notice that rows 1, 2 are recognised as numbers, 3 and 4 are recognised as strings, and 5 is recognised as a date.

For the strings, you're basically stuck. Excel doesn't recognise them as dates so it can't change the formatting. For instance, if you select the column, then in excel choose to "format" it as a short date, you'll see that the strings just don't change.

The other rows, however, are now nicely formatted dates. Row 2 and 5 are correct, row 1 is incorrect. This is because MATLAB and Excel use different "base date" for numbers (see doc xlswrite).

Of course if you want to show a time (rather than the date), you simply need (in excel) to format the column as "time", and you can customise the time format in excel. When you do that, you'll notice that the actual day of the date doesn't show and all the times show the same thing in whichever format you choose.

Does this solve things for you?

[Jovanie Josol]

THHANKS Sven! I tried the codes in the command windows. and ds2 is what I really needed. but when I tried again to import only ds2 the excel printed every characters in every cell. same as when i tried this last time in m.file, it will display series of 2 digits. Im just curios. actually I used cellstr now after ds2. and it is working. i have now an array of strings.

[Sven]

Jovanie, glad it's working for you. To clarify: the ds2 variable I showed is an example of the wrong way to do it.

Even though you want your time to be shown in excel in 'HH:MM PM' format, it is not a good idea to send your data already in that format as a string. That's because excel doesn't interpret that string as an actual date... it just interprets it as a string (just like if you sent the string "hello").

Instead, you should use, say dn2 or ds3. When imported into excel, both of these will be converted into a date in excel. Then you can set the formatting of the cell in excel.

Basically, if you want your date shown in a particular way in excel, don't do the formatting in MATLAB. Just send the date unformatted, and do the formatting in excel.

[Jovanie Josol]

thanks sven.