{"id":61,"date":"2008-04-14T14:41:14","date_gmt":"2008-04-14T19:41:14","guid":{"rendered":"http:\/\/blogs.cae.tntech.edu\/mwr\/2008\/04\/14\/converting-national-instruments-lvm-timestamps-to-excel\/"},"modified":"2024-10-27T14:26:19","modified_gmt":"2024-10-27T14:26:19","slug":"converting-national-instruments-lvm-timestamps-to-excel","status":"publish","type":"post","link":"https:\/\/sites.tntech.edu\/renfro\/2008\/04\/14\/converting-national-instruments-lvm-timestamps-to-excel\/","title":{"rendered":"Converting National Instruments LVM Timestamps to Excel (UPDATED: and Matlab)"},"content":{"rendered":"

A few days ago, I had a student looking into what would be required to periodically log some temperatures and pressures from a long-running furnace experiment, so that he doesn’t have to babysit it and come back every 30 minutes to record his data. We borrowed a National Instruments USB-6008 data acquisition device and downloaded NI SignalExpress LE to try some things out.<\/p>\n

There wasn’t much of a problem with actually capturing the data, but the timestamps (in column 1) were odd-looking:<\/p>\n

\nLabVIEW Measurement\t\nWriter_Version\t0.92\nReader_Version\t1\nSeparator\tTab\nMulti_Headings\tYes\nX_Columns\tOne\nTime_Pref\tAbsolute\nDate\t2008\/04\/11\nTime\t16:24:24.354863\n***End_of_Header***\t\n\t\nChannels\t1\t\nSamples\t100\t\nDate\t2008\/04\/11\t\nTime\t16:22:45.354864\t\nX_Dimension\tTime\t\nX0\t0.0000000000000000E+0\t\nDelta_X\t1.000000\t\n***End_of_Header***\t\t\nX_Value\tVoltage - Dev1_ai0\tComment\n3.29079376535486410E+9\t2.64872102540972910E+0\n3.29079376635486410E+9\t2.64872102540972910E+0\n3.29079376735486410E+9\t2.64872102540972910E+0\n3.29079376835486410E+9\t2.64872102540972910E+0\n3.29079376935486410E+9\t2.65280301912685700E+0\n3.29079377035486410E+9\t2.65280301912685700E+0\n<\/pre>\n
There may be a simple way to reformat them inside SignalExpress LE, but it wasn’t obvious enough when we looked. So here’s one solution.<\/p>\n
The primary difference between National Instruments’ timestamp format and Excel’s is that NI counts a real-valued number of seconds since January 1, 1904, while Excel counts a real-valued number of days since January 1, 1900. So
\nthe formula (A6\/86400)+365*4+2 (convert seconds into days, add four years plus two leap days for 1900 and 1904) will convert NI’s timestamp value of 3290793765.35486 in cell A6 into an Excel equivalent 39549.8908, or April 11, 2008.<\/p>\n
But that’s not quite enough. The test run we made was at 4:22 PM, and that’s nowhere near 89% of a full day. Sure enough, putting 39549.8908 into a time format gave us 9:22:45 PM, a full five hours ahead of local time. So there’s a timezone shift in there, too, and we’re currently on GMT-5. So strip off the fractional part of the Excel timestamp, offset it by 5\/24, and convert it back into a time. The final resulting spreadsheet in tabular form:<\/p>\n\n\n\n\n
<\/td>\nA<\/td>\nB<\/td>\nC<\/td>\nD<\/td>\nE<\/td>\nF<\/td>\nG<\/td>\nH<\/td>\nI<\/td>\nJ<\/td>\n<\/tr>\n
5<\/td>\nOriginal LVM Time<\/td>\nOriginal LVM Voltage<\/td>\nConvert Secs to Days<\/td>\nOffset by 4 Years (plus 2 leap days)<\/td>\nFinal Date<\/td>\nTime (before timezone offset)<\/td>\nDecimal part of Timestamp<\/td>\nOffset for Timezone<\/td>\nFinal Time<\/td>\nFinal Date\/Time<\/td>\n<\/tr>\n
6<\/td>\n3290793765.35486<\/td>\n2.64872102540972<\/td>\n=A6\/86400<\/td>\n=C6+365*4+2<\/td>\n=D6\t<\/td>\n=D6<\/td>\n=D6-ROUNDDOWN(D6,0)<\/td>\n=G6+$C$3\/24<\/td>\n=H6<\/td>\n=ROUNDDOWN(E6,0)+H6<\/td>\n<\/tr>\n<\/table>\n
where cell C3 contained a -5 for our timezone shift from GMT. Columns E, F, I, and J were all in date, time, or date\/time format as needed. The result in cell J6 could be cut down to =(A6\/86400+365*4+2)+$C$3\/24<\/code> if you’re in a rush to just convert it.<\/p>\n
There may be a math or other error that’s been compensated for somewhere in here, since I’m not 100% positive about the 2 leap days. But it does equal out down to the second.<\/p>\n
Matlab Addendum:<\/strong><\/p>\n
Matlab has another time format altogether — it uses a real-valued number of days since the zeroth of January, year 0000, whatever that means:<\/p>\n
\n>> datestr(0,0)\n\nans =\n\n00-Jan-0000 00:00:00\n\n>> datestr(1,0)\n\nans =\n\n01-Jan-0000 00:00:00\n\n>> datestr(1\/86400,0)\n\nans =\n\n00-Jan-0000 00:00:01\n<\/pre>\n
Since that goes back far enough to include the current Gregorian calendar, Julian calendar, and possibly even the pre-Julian Roman calendar standards, the conversion equation is a bit more obtuse (in particular, the 97 day offset was found entirely by trial and error):<\/p>\n
\ntzoffset=-5;\ndata=dlmread('test1.csv',',',1,0);\ntime=data(:,1);\ntemperature=data(:,2);\ntime=time\/86400+365*1905+97+tzoffset\/24;\nplot(time,temperature);\ndatetick('x',0);\ngrid on;\nxlabel('Time');\nylabel('Temperature');\n<\/pre>\n
resulting in the correct plot for the brief experiment:
\nTime and Temperature Plot<\/p>\n","protected":false},"excerpt":{"rendered":"
A few days ago, I had a student looking into what would be required to periodically log some temperatures and pressures from a long-running furnace experiment, so that he doesn’t have to babysit it and come back every 30 minutes to record his data. We borrowed a National Instruments USB-6008 data acquisition device and downloaded … <\/p>\n
Continue reading “Converting National Instruments LVM Timestamps to Excel (UPDATED: and Matlab)”<\/span><\/a><\/p>\n","protected":false},"author":87,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,11,12],"tags":[],"class_list":["post-61","post","type-post","status-publish","format-standard","hentry","category-excel","category-matlab","category-national-instruments","entry"],"_links":{"self":[{"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/posts\/61","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/users\/87"}],"replies":[{"embeddable":true,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/comments?post=61"}],"version-history":[{"count":1,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/posts\/61\/revisions"}],"predecessor-version":[{"id":471,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/posts\/61\/revisions\/471"}],"wp:attachment":[{"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/media?parent=61"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/categories?post=61"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sites.tntech.edu\/renfro\/wp-json\/wp\/v2\/tags?post=61"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}