MATLAB: Import date from matlab to ms excel in the form ‘HH:MM PM’ using xlswrite

'hh:mm pm'clockdatestrxlswrite

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');

Best Answer

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?
Related Question