datestr not writing to excel properly
in [Matlab]
|
From: Gary Powers on 23 Mar 2010 06:19 On Mar 23, 10:12 am, Gary Powers <gary.power...(a)yahoo.com> wrote: > On Mar 23, 9:39 am, TideMan <mul...(a)gmail.com> wrote: > > > > > > > On Mar 23, 9:38 pm, Gary Powers <gary.power...(a)yahoo.com> wrote: > > > > On Mar 22, 6:58 pm, TideMan <mul...(a)gmail.com> wrote: > > > > > On Mar 23, 7:04 am, Gary Powers <gary.power...(a)yahoo.com> wrote: > > > > > > Hi, > > > > > > I'm trying to write time data to an excel file. Example code below > > > > > shows formatting: > > > > > > exceldata={'Time','Data'}; > > > > > for i=1:5 > > > > > pause(0.2) > > > > > time=datestr(now, 'HH:MM:SS.FFF'); % time in > > > > > hour:minute:second.milisecond format > > > > > data=rand(1); > > > > > exceldata(size(exceldata,1)+1,1:2)={time,data}; > > > > > xlswrite('test.xls',exceldata); > > > > > end > > > > > > Now if you look at the 'time' variable in the command window and in > > > > > the excel file, the hours are cut off in excel! > > > > > > e,g,: > > > > > in command window 18:00:49.922 > > > > > in excel 00:49.922 > > > > > > If anyone can fix this, it would be an enourmous help. > > > > > > Thanks > > > > > One way to do it is to send Excel days to Excel: > > > > xlswrite(xlsfile,now-693960) > > > > where 693960 has been subtracted to convert Matlab days to Excel days. > > > > Now, you can format the date/time in Excel however you want.- Hide quoted text - > > > > > - Show quoted text - > > > > The problem is that it works for the time format: > > > > time=datestr(now, 'HH:MM:SS.FFF AM'); % time in > > > > but not: > > > > time=datestr(now, 'HH:MM:SS.FFF'); % time in > > > > The time strings look fine in matlab, but without the AM/PM on the > > > end, the formatting is lost in excel.. I think excel is automatically > > > converting to its own time number format- when the AM/PM is on the end > > > of the string excel just accepts it as a string. > > > > How can I force excel to read my time format as a string? > > > Did you even read my post?- Hide quoted text - > > > - Show quoted text - > > Yes I did, however I am aiming for a solution that does not require > additional formatting once the excel file is written.- Hide quoted text - > > - Show quoted text - I found a workaround, not the best but does the job i suppose. Just importing time(9)=':'; gets rid of the decimal point in the time string, and excel then doesn't assume one of it's own date formats. Im pretty sure the cell format can be forced via an excel COM object manipulation ( excelObj = actxserver('Excel.Application'); ), although it looks like it would consume far more time than I have available right now |