Convert Excel Time Stamp to LabVIEW Time Stamp

[Prabhakant_Patil]

Hi All

This VI will covert Excel time stamp into Labview time stamp.

Note -  Excell date format should be MM/DD/YYYY

If Date format is changed, you have to change in the code.

There are Two example

1. Using ActiveX control

2. Using Report Generation Toolkit.

If You Have any suggestions or Query, the please let me know.

Please give your comments

Regards

Prabhakant

[GerdW]

Hi Prabhakant,

I found this entry due to the latest NI newsletter.

Sorry for being harsh, but this example isn't the greatest. It doesn't work if you don't use English settings in Windows (and no: it does not help to set the time stamp display in Excel to English M/D/Y). For my opinion is overly crowded and, hey, why do you need 1MB zipped filesize to convert some Excel values to LabVIEW timestamp?

I prefer this:

You simply read the excel value as numeric (instead of formatted string), then it's easy to convert...

[FabiolaDelaCueva]

GerdW,

For some reason, I can't drag your VI Snippet into my LabVIEW 2011 blockdiagram and if I try downloading the picture it doesn't work either. I was trying to figure out what the "UTC-OFF" function does. Would you please attach your VI or try uploading your snippet again?

Thanks,

Fab

[Thoric]

subVIs aren't embedded in snippets anyway (according to the LabVIEW 2011 help - I have little experience with them to know off-hand), so GerdW will need to provide the "UTC-OFF" subVI as a downloadable VI anyway.

[GerdW]

Hi Fabiola,

"UTC-Off" calcs the offset of local time to UTC:

All the functionality shown here is based on information found in the LabVIEW forum (just to give credits...)!

[Thoric]

(post removed)

[FabiolaDelaCueva]

GerdW,

Thanks!

I used to be able to drag the snippets directly from Internet Explorer, but I guess that feature has been lost. I needed to right click on the snippet and select "save target as" and then drag the image from the folder I downloaded it to into my block diagram. It worked!

Thoric was right, the first snippet would result in a question mark, because the subVI was not in my system.

Thanks for providing the "complete" solution.

Regards,

Fab

[FabiolaDelaCueva]

One more thing, I love to label constants, because I forget where numbers might have come from. So, in case there are other people like me out there that want to know why we are using 86400 and 1462 to calculate the offset to local time, here is the explanation:

86400 = seconds in one day

1462 = days in 4 years + 1 day to account for leap year

The 1462 is subtracted to account for the difference between systems using 1900 Date System and systems using 1904 Date System. More information can be found here: http://support.microsoft.com/kb/180162

[GerdW]

Hi Fabiola,

you pointed out one of the reasons I didn't make my CLD so far

One more comment to "UTC-Off": it's output is labelled "offset / s", lost it due to snippet creation (as well as VI documentation)...

I created polymorphic functions based on the shown snippet to support also conversion of arrays of timestamps, and of course there's also the reversal functionality (LV-Timestamp to Excel) in my user.lib.

[FabiolaDelaCueva]

I tend to over document But it has saved me a lot of time while troubleshooting. While now 86400 is obvious to me that it is the number of seconds in a day, once I spent a long time with a customer troubleshooting his code and we couldn't understand why he was using that constant, until I started typing into Google and it told me right away that it was the number of seconds in a day. Better to have it right there on the code.

One more thing, after playing a little bit more with your code, there is a small change that you need to do if you want to get the exact timestamp down to the fraction of a second, it turns out the 1462 needs to be replaced by 1462.0000000033.

You will see what I mean when you create subVIs and start entering different numbers in Excel. The code with 1462 has a slight error on the seconds.

[Prabhakant_Patil]

Your suggestions are really good.

It improves the knowledge

Thanks

Prabhakant

[GerdW]

Thanks Fabiola,

for the more precise constant (1462.0...033).

In my applications we only use the date (so far), so I missed that 280µs offset. (Especially as Excel only allows for displaying ms in a timestamp display.)

One note:

That difference seems to be related to limited accuracy of floating point numbers. I tested several timestamps with my VI and Excel2010 and both gave same results (apart from rounding issues - and I tend to trust LabVIEW more than Excel 😞

[FabiolaDelaCueva]

You are right GerdW, I should have kept the values I was playing with yesterday that gave me the odd results and let me to those decimals. I was playing with random dates...

Today I went back to using your original code without the extra decimals and I see something odd on some time stamps. Can you double check this, I have been sick and mabye my medication is playing games with my head, here are the results I am getting. I highlighted the odd ones. At first I thought it was a difference on time settings, but that would make all the results be off by one hour, except some are correct:

Do you get the same results?

Fab

[DFGray]

Fabiola, it looks like a daylight savings time problem.  The times which occur during DST are correct.  Unfortunately, I don't have time to track it down for you.  If you find a LabVIEW bug, let me know and I will report it.

[FabiolaDelaCueva]

That is probably it and makes sense! One more thing to keep in mind with these conversions.

Thanks and I will keep away from the forums until I feel better

[rolfk]

The problem is that the %z format returns the timezone offset for the current time. To get the timezone offset of any timestamp you should instead convert such a timestamp into UTC and non UTC timerecords and calculate the difference between those two. This will fail on older OS platforms. XP knows one DST period for all years so there might be a miscalculation for periods that used different start and stop times for DST, but Windows Vista and newer support correct DST periods for quite a few years back.

Better yet use directly Seconds to Date/Time with toUTC true and then the inverse with isUTC false to get a correct LabVIEW timestamp. This still has the potential to go awry for an hour around the time DST gets effective and also when it ends.

[GerdW]

Hi Fabiola,

it's a pity we're not able to attach VIs here in the community.

So I have to attach a snippet again:

That's the "full" UTC-Off.vi with both versions of offset calculation included.

[David_L]

-- Edit:  I had a tip here, but quickly discovered that I was wrong.  Sad when that happens. 

Nevermind!

[rolfk]

This UTC offset only does the same as the %z format specifier for the Data/Time String primitive, namely returning the timezone offset for the current time. If you want to get the timezone offset of any arbitrary timestamp you need to do something more along these lines.

[GerdW]

Thanks to all.

Somehow I missed the DST problem with my use-cases so far...