Excel Date Time routine

[jbrohan]

Routine to convert Excel dates to LabVIEW

Excel has the alluring and simplistic notion that time can be expressed as a fractional number of days since 1900 01 01. LabView starts 4 years later. So the number of days between these two is 365*4. 1900 was not a leap year. Unfortunately this is not correct and we need to subtract 2 more days to get it right. Happy to get the truth of this!

The time is represented as a decimal fraction in Excel eg 0.5 = 12:00 midday. There are 86400 s in a day so if you multiply this fractional part by this number you'll get the number of seconds. Well nearly!

What LabVIEW gives is the time where I am (my computer thinks it is located) when it is x number of seconds past 1904/1/1 (midnight) in Greenwich England. So we need to add the 5 hours time zone difference to get Montreal time. But it's sometimes summer time so we need to subtract one hour if it is. The routine takes the simplistic notion that if a time turns out (according to

[jbrohan]

The original routine had the sign wrong on the Time Zone.

Here is the updated UTC Time Zone VI and the ExcelDateTime.vi

This converts dates from Excel numeric value (Re Format a column of dates in a spreadsheet to Numeric and these numbers are the excel dates I'm starting with). The conversion is to LabVIEW Time Stamp. There is a reverse function included in the same VI.

It is possible to determine your time zone from the G function Seconds to Date/Time.

1)feed in Zero this will give 1904-01-00 0:00:00 at Greenwich, but if you are in Montreal it gives 1903-12-31 20:00:00. (In the summer time) That's -4 hours offset.

2)Leave the Seconds input unconnected (Now) and examine the output Time Structure for DST. Add another hour for the Daylight Savings Time and you arrive at -5 which is out time zone.

3)This routine should work all over the world (Maybe even in Newfoundland which is 30 minutes ahead of Montreal.)

Download File:post-230-1122943401.zip

[silver]

Hello,

is it possible that I see milliseconds in the time stamp?

e.g 12:10:30,100

I ask this since I am plotting a chart with 2 variables over time a-axis which looks like this:

x1,y1, time

x2,y2, time+100 ms

x3,y3, time+200 ms

...

...

x1000000,y1000000, time + 100.000 sec

e.g

100,102, 1,5000000 (1,5 is the Excel interpretation of the time which means 1 day 12 hours after 01.01.1900)

100,102, 1,500000001

so as you see I have 10 plots in a second and I want to record each row in a .CVS or Excel sheet. I couldn`t find millisecond interpretation , I guess it doesn`t exist in LV 7.1 ?!

The second point I want to ask is , I managed to convert the LV time, to Excel Decimal format (e.g 3600,512342 == 10.10.2006 12:17:46)

Now I want to save them in .CVS or EXCEL format, X,Y,and time values are arrays , how do you advise me to save them in excel file? and what is with milliseconds?

Thanks in advance...

silver

[Rolf Kalbermatter]

QUOTE(jbrohan @ Aug 1 2005, 07:43 PM)

The original routine had the sign wrong on the Time Zone.

Here is the updated UTC Time Zone VI and the ExcelDateTime.vi

This converts dates from Excel numeric value (Re Format a column of dates in a spreadsheet to Numeric and these numbers are the excel dates I'm starting with). The conversion is to LabVIEW Time Stamp. There is a reverse function included in the same VI.

It is possible to determine your time zone from the G function Seconds to Date/Time.

1)feed in Zero this will give 1904-01-00 0:00:00 at Greenwich, but if you are in Montreal it gives 1903-12-31 20:00:00. (In the summer time) That's -4 hours offset.

2)Leave the Seconds input unconnected (Now) and examine the output Time Structure for DST. Add another hour for the Daylight Savings Time and you arrive at -5 which is out time zone.

3)This routine should work all over the world (Maybe even in Newfoundland which is 30 minutes ahead of Montreal.)

Your DST calculation might be flawed a bit. Before LabVIEW 7.0 LabVIEW always used the current time to evaluate if a timestamp had to be adjusted for DST. So if you run a VI using that in the summer it will return a different DST status and timezone offset for a specific timestamp (not the current one) than when you execute it in the winter.

In LabVIEW 7.0 I think they remedied that by taking the DST status that was actual at the time the timestamp itself represents except for timestamps that are before 1970 or something like that. For those it still uses the current time independant of the DST status that was actual at the time represented by the timestamp itself.

Rolf Kalbermatter

[Branson]

QUOTE (silver @ Dec 12 2007, 11:42 PM)

Hello,

is it possible that I see milliseconds in the time stamp?

e.g 12:10:30,100

yes

In Excel, choose Format Cells..., in the Category box choose Custom, in the Type: box paste in " m/d/yyyy h:mm:ss.000 "