datestr not writing to excel properly
in [Matlab]
|
From: Gary Powers on 22 Mar 2010 14:04 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
From: TideMan on 22 Mar 2010 14:58 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.
From: Gary Powers on 23 Mar 2010 04:38 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?
From: TideMan on 23 Mar 2010 05:39 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?
From: Gary Powers on 23 Mar 2010 06:12
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. |