Labview Timestamp to Excel Time Formatted Cell

[id]

Hello, I am in need of a VI that can convert a standard Labview Timestamp used ion a Labview VI that when exported to Excel via a tab delimitted text file or some equivalent will be recognizable by excel as a timestamp in its cells.  Has anyone gotten this done?

[James_W]

Hi id 

You just need to convert the timestamp to a string with a format the Excel recognises as a date.

Use the format string function and something like:

 %<%d/%m/%Y %H:%M:%S>T

play around with the function until you get a format that works - better still look at excel to see what format it displays, and specify this as the explicit format to convert the string to.

James

[DFGray]

Excel will actually format time in as many ways as LabVIEW to accomodate local preferences.  Under the hood, Excel stores times as a floating point number with the integer being the day and the fraction the fraction of a day.  The epoch is January 1, 1900 = 1.0.  In addition, due to a variety of interesting historical decisions, Excel assume 1900 is a leap year, which it is not, adding an extra day to the number for dates from March 1900 forward. A web seach will give you a plethora of information, such as this link.

LabVIEW formats time as the number of seconds since midnight January 1, 1904 GMT.  The timestamp data type is a 128 bit fixed point number with the decimal in the middle (64-bit integer and fraction).  Converting from LabVIEW's internal format to Excel's is left as an exercise to the reader, although the solution has been posted on these forums more than once ;).

However, as James W said, it is probably easier to format to a text string and use that.  This requires no action in Excel, whereas the converted number would need to be formatted to a date once imported to Excel.  You may have trouble with time zones, however, since I believe Excel uses local time, but LabVIEW uses GMT.

[Micael_]

Try "%Y-%m-%d %H:%M:%S"

[id]

Thanks for the prompt exact answer - Kudos to u!

[RTSLVU]

Here is the diagram snippet and a vi that I use a lot.

The time stamp will be generated as the data is saved or you can pass your own to it if you need more accurate timstamp like when the measurement is taken.

This seems to open fine in Excel and contains the default time and date format (5/10/2010 7:29:54)

Message Edited by RTSLVU on 05-10-2010 07:36 AM

[James_W]

---

id wrote:
Thanks for the prompt exact answer - Kudos to u!

---

Kudos are awarded by clicking on the stars on the right, not just writing "Kudos to u"

James