Excel VBA read LabView date time stamp

To read a binary file you have to know the structure of it. In my case I was trying to read binary file written by LabVIEW-based software which records measurement data. The file represent a one-dimensional array of clusters, the cluster has two elements: TimeDate stamp and double precision number.

According to the following link http://www.ni.com/tutorial/7900/en/

LabVIEW 7.0 or earlier used a 64-bit double (DBL) to represent time, yielding 15 digits of precision. The number of seconds between 1st Jan 1904 (the time stamp Epoch or year zero) to 1st Jan 2000 is 3027456000. Representing this as a DBL would use 10 out of the 15 digits of precision. That leaves a very small resolution space to perform hardware timings using most of the resolution by simply going from 1904 to today.  Representing time as a DBL was not ideal since it did not meet industry requirements.

In MS office the date reference is year 1900, while LabVIEW date reference is year 1904. So, in calculations we will compensate this date reference difference. Number of days difference is 1462 days.

' Function to convert binary to decimal Function BinaryToDecimal(ByVal Binary As String) As Double Dim BinaryNum As Double Dim BitCount As Integer For BitCount = 1 To Len(Binary) BinaryNum = BinaryNum + (CDbl(Mid(Binary, Len(Binary) - BitCount + 1, 1)) * (2 ^ (BitCount - 1))) Next BitCount BinaryToDecimal = BinaryNum End Function

' Function to convert 64-bit binary to double-precision float Function BinaryStringToDouble(ByVal BinaryString As String) As Double Dim i, Sign, Exponent, BitCounter As Integer Dim Fraction, DoubleNo As Double 'Read number sign Sign = (-1) ^ CLng(Mid(BinaryString, 1, 1))     'Most-significant bit ' Read exponent Exponent = BinaryToDecimal(Mid(BinaryString, 2, 11)) ' Read the fraction Fraction = 0 BitCounter = 0 For i = 13 To Len(BinaryString) BitCounter = BitCounter + 1 Fraction = Fraction + (2 ^ (-BitCounter)) * CDbl(Mid(BinaryString, i, 1)) Next i BinaryStringToDouble = Sign * (1 + Fraction) * 2 ^ (Exponent - 1023) End Function

' Function to convert LabView date-time-stamp to string date and time Function DoubleToDateTime(ByVal LVDateTimeStamp As Double) As String    ' input LabVIEW DateTime stamp (64-bit double precision number) Dim RefOffset As Double Dim MSDateTimeStamp As Double Dim MSDate As Double Dim DateString As String Dim DayElapsedTime_sec, Hours, Minutes, Seconds As Double RefOffset = 126316800     'Reference offset in seconds 1462[days]*24[h/day]*60[Min/h]*60[sec/Min] 'Convert it to Microsoft DateTime stamp: number of seconds from 1-Jan-1900 MSDateTimeStamp = LVDateTimeStamp + RefOffset MSDate = Application.WorksheetFunction.Floor(MSDateTimeStamp / 86400, 1)    ' number of days from 1900 ' 86400: number of seconds per day DateString = CStr(CDate(MSDate)) DayElapsedTime_sec = MSDateTimeStamp - MSDate * 86400+7200    'Egypt time = UTC time + 2 hours (7200 sec) Hours = Application.WorksheetFunction.Floor(DayElapsedTime_sec / 3600, 1) Minutes = Application.WorksheetFunction.Floor((DayElapsedTime_sec - Hours* 3600) / 60, 1) Seconds = DayElapsedTime_sec - Hours * 3600 - Minutes * 60 DoubleToDateTime = DateString + " " + CStr(Hours) + ":" + CStr(Minutes) + ":" + CStr(Round(Seconds, 0)) End Function

References:

http://www.binaryconvert.com/

https://en.wikipedia.org/wiki/Double-precision_floating-point_format

Keywords:


VBA Binary file read

Read binary file written by LabVIEW

Parse (parsing) binary file

Read LabVIEW binary file using Excel VBA

Read data in binary file

Read IEEE754 Double precision 64-bit number from binary file

LabVIEW DateTime stamp to binary