LabVIEW
Converting LV Time Stamp from Excel
Solved!
So you have to do the math on the double before you paste or post it into Excel. I had just tried to Get Date/Time and convert to double to display in a numeric. Copy that direct as text into Excel will not convert as was mentioned. Quick snippet to show if you copy "Direct Copy" vs "Direct Copy post" and try to format as time.
If you use ActiveX and send Excel the LabVIEW Timestamp format (brown wire, coerced into Variant at the Value2 Property node) then it will convert it for you. Otherwise yes you need to do math to convert it (and handle some exceptions).
I would assume dotNet is similar.
Basically the LabVIEW timestamp is number of seconds since January 1, 1904 GMT. An Excel timestamp is number of days since January 1, 1900 with a little anomaly that they "forgot" about that 1900 was not a leap year. Supposedly this is the same definition that was used in the Lotus 123 software too, so it is most likely not so much about forgetting the leap year rules by the Excel developers as rather wanting to be compatible with the market leader back in that time.
Apple avoided the problem of the irregular leap year by moving the epoch for its timestamp 4 years into the future and LabVIEW adopted that since LabVIEW was originally developed on Macintosh and therefore used that format for timestamps.
Something to think about:
Rather than remembering the formula and constants to convert the time stamp to seconds from Jan 1, 1904 (or is it Jan 1, 1900??), I format the timestamp directly to text. Don't know which method is better or worse, slower or faster. But that really doesn't matter since the Write to Excel will be many orders of magnitude longer.
I've been doing it this way for over 20 years without any problems. The only manipulation of the timestamp I do would be changing it from local to UTC. You would also need to make an exception on the days that time changes from Standard to Daylight Savings Time (or vice versa). My simple workaround is to always set my computer's "Time Zone" to UTC. Problem solved.
NOTE: everyone seems to use %f for the format string. That defaults to 6 decimal places. In the below example, that round off error shows up as a delta of about a 16ms from the Excel to LabVIEW timestamp. The delta could be more or less depending on the amount of round off. My understanding is that Windows time is not accurate below 10ms anyway. But if that delta matters, then you should use something like %.9f. The direct text format saves it to 10+ digits, and the timestamps always match down to the millisecond.
It sure does present some challenges, the first is understanding the differences between all these platforms.
Wonder why there are no official NI vi's included with LabVIEW that deal with coding challenges like this that a lot of developers run into and have to reinvent the wheel over and over again - rhetorical question.
Anyways, I gathered some bits and pieces and this is what I came up with to work in my time zone which is where the app will function for its lifetime - I had tweak the timestamp constant to get to my zone, the case structure deals with DST.
Thanks to all for your input and help!
@FishBonze wrote:
It sure does present some challenges, the first is understanding the differences between all these platforms.
Wonder why there are no official NI vi's included with LabVIEW that deal with coding challenges like this that a lot of developers run into and have to reinvent the wheel over and over again - rhetorical question.
Anyways, I gathered some bits and pieces and this is what I came up with to work in my time zone which is where the app will function for its lifetime - I had tweak the timestamp constant to get to my zone, the case structure deals with DST.
Thanks to all for your input and help!
Hey, I believe Altenbach said there was a time when you couldn't delete wires, so consider yourself lucky! 😄
@FishBonze wrote:
Wonder why there are no official NI vi's included with LabVIEW that deal with coding challenges like this that a lot of developers run into and have to reinvent the wheel over and over again - rhetorical question.
Most likely because it is an almost unsolvable problem really. There used to be some VIs around even from NI that did this, hacked together and working if your Windows country settings were just right, AND your timezone wasn't off from CDT (Texas time) AND the summer time wasn't configured differently in your system AND a few other things.
Taking all these things into consideration even without accounting for even more weird custom settings in Windows that can throw this off even more, and "improvements" in time handling in Excel over the years in new versions is already a huge challenge!
