How to write and format to excel

[RTSLVU]

You are going to run into an old issue with LabView timestamps in Excel. Sure formatting them as a string "works" but you will find they do not work the same once you have hem in Excel. The problems is LabView does not use OLE date format, it uses a UTC date.

OLE (Excel) date format is a double-precision floating point number that counts the time from 30 December 1899 00:00:00.
Thus, a value of 0.0 corresponds to 30 December 1899 00:00:00.

The UTC date that is returned from the LabVIEW functions is the number of seconds elapsed since 12:00 a.m., Friday, January 1, 1904.
Thus, in this case, a value of 0.0 corresponds to 01 January 1904 00:00:00.

This means you have an offset between a LabVIEW timestamp and the OLE date.

Basically, whatever date you get from LabVIEW you need to add the number of seconds between 30 December 1899 00:00:00 and 01 January 1904 00:00:00 to get the corresponding OLE date value (in floating point) and adjust for your timezone.

I have attached a VI I wrote a long time ago that adds a time stamp to an array and writes it to an Excel compatable file.

[RavensFan]

He isn't putting LabVIEW timestamps into Excel.  He is converting the LabVIEW timestamp into a string, then putting that string into a text file.  Excel will open that text file and import the string as an Excel time.

[RTSLVU]

---

@RavensFan wrote:

He isn't putting LabVIEW timestamps into Excel.  He is converting the LabVIEW timestamp into a string, then putting that string into a text file.  Excel will open that text file and import the string as an Excel time.

---

Yes but... Unless something has changed in newer versions of Excel a time/date string is not treated the same as an OLE timestamp.

I used to do that exact thing all the time until an engineer I was working with very angerly told me that I was doing it wrong. At first I did not understand beecause it looked okay to me in Excel. Then he tried to make a chart in Excel using that time/date string and I saw what he meant. You can not reformat the time stamp in Excel if it is a LabView time/date string and it really messes up the X axis scale when charting. 

[RTSLVU]

This blurb I ran accross this morning explaines it better.

The question was on how to make a "Date Formatted" chart in Excel.

In the conclusion the author pointed out  

"Before making a date based chart, look carefully at the date data. Is it all in Excel date format or as text? Make sure all the dates are in the format Excel recognizes as dates – not text. "

Excel only recognises OLE formatted time stamps as dates or time, the LabView timestamp is treaded as text when put in Excel.

This can make graphing data difficult. 

[RavensFan]

All I can say is that I've never had problems graphing text-based dates in Excel.  At most, I've had to reformat the date/time column so it displays the date with a date-based format rather than as a text string.