LabVIEW Time to Excel Time Format

[Justin_P]

Overview

This VI allows you to convert the timing from LabVIEW to excel Time.

Description

The time format in LabVIEW is different from the time format in Excel. If you simply convert the timestamp cluster to a double and paste that data into an Excel it wont make sense. This is because the Excel time format is based on the number of days since 1 Jan 1900. The LabVIEW time is based on the number of seconds that have passed since 1 Jan 1904. This vi converts the LabVIEW time to Excel time.

You can find another implementation with daylight saving here.

Requirements

LabVIEW 2012 (or compatible)

Steps to Implement or Execute Code

1. Download and open the attached VI LabVIEW Time to Excel Time_LV2012_NI Verified

2. Run the VI

Additional Notes or References

Block diagram

**This document has been updated to meet the current required format for the NI Code Exchange. **

[KenGlaze]

This is exactly what I was looking for but the VI you posted was made by version 8.5 and I have 8.2 so I cannot open it. Maybe you could post a screen shot of the block diagram with some comments?Thank You

[Justin_P]

Hope this helps

[Reynaldo]

Justin,

I ran your LVtime2Exceltime8.2.vi. My labview is showing the time and date on current labview time:

8:16:33:398 AM

9/19/2008

and the time in excel format

39710.6365

I copied 39710.6365 and pasted in excel cell and click format cell, select time or date 3/14/01 1:30PM.

My cell in excel is showing

9/16/08 3:16 PM

even thought I ran your program in the morning at 8:16:34 AM but my cell is showing at 3:16 PM. It should be 8:16 AM.

Your vi was not exactly correct and did not match with the time of labview and excel format.

Thanks,

Reynaldo

[Philip_Courtois]

Justin,

You are on the right track, but you are missing a few things.

First of all, LabVIEW timestamps are based on the number of seconds since Jan 1st 1904 Universal Time (UTC), so you need to make sure to add the local UTC offset (including the sign) before converting it to the Excel format.

Also, Excel starts at January 0th (not 1st) 1900. Can't seem to find that date mentioned in any history books, but that's the date Mr. Gates decided to use as his reference.

Last and least, the label in your example that says "Seconds in a year" needs to be "Seconds per day".

That should be all for now,

Cheers,

- Philip

[JÞB]

A version is available here: http://decibel.ni.com/content/docs/DOC-12365

This version reports time zone information, accounts for DST and corrects for the leapyear bug in Excel

[Hitesh_Dhola]

Nice one.. But LV RT has 64 bit timestamp and I am not able to convert it in Date, time , seconds, milliseconds or us

[qing_shan61]

to get milli-second in Excel, you need to format the cell (or cells) to a customer format, such as

dd/mm/yyyy hh:mm:ss.000

[0Forest0]

Excel have bool flag "Date1904". It is property of workbook.

Date in 1900 year = Date in 1904 year + 1462 days.

And we shell save into Excel and load later Time zone+DaylightSavingTime.