Function EFU_to_MS_serial(efu_time As Double, utc_correction As Double) As Double
'
' Excel VBA function:
'
' Converts EFU date-time values from VoidTools utility program *Everything* export of file lists to Microsoft serial dates.
' (Import them as floating point values rather than integers or strings, or convert in the function call with *1 multiplication).
'
' Background: The conversion function offered on the Voidtool forum (circa 2014) is incorrect (and has a typo so I doubt whether is
' was ever compiled. And it is much too complicated.)
'
' EFU file times are in 100 ns counts from a base time of 1-Jan-1601
' MS serial times are an integer number of days + fractional part of a day since a base time of 1-00-1900.
'
' The main thing is to correct for the difference in base times.
'
' Since Excel and VBA cannot perform date arithmetic before their base time I did a future based compuation:
' Since 1900-1601 = 299 years, there should be approximately 299 x 365.2422699 = 109207.44 days after this
' (fictional) '0' base MS date, as well as before. Comparing a conversion with this Base_Time_Offset to an
' actual Microsoft file time reveals (vs an Everything exported file list) that the actual offset here should be
' 109205 days. This then needs to be converted to EFU units of 100ns.
'
' See:
https://en.wikipedia.org/wiki/Sidereal_time
'
' But then the times are 'off' by exactly four hours. This is easily explained as the difference between UTC time and my East
' coast location: a 4.00 hour correction, including for now DST. Remember 4/24 of a day!
'
' NOTE: I made the utc_correction a positive number and a subtraction in the implementation. More properly it is a negative
' correction as UTC is always larger. Feel free to change, but document what you do.
'
' TEST: I exported 100 mpg files from Everything and compared their modified times converted with this fuctions with what
' Windows File Explorer reports. Deltas were 00:00.0 to 00:01.0 seconds, Everything values were always slightly larger.
' Average was 0.5 second. This is close enough for my needs. I could not tell if the lowest order binary digits of the EFU
' values contained the UTC embedded corrections or not.
'
' Feel free to modify this function to try for automatic, location based utc correction but note that the correction should be
' based on the utc in effect at the relevant file time, not the current system time. Modified, created, accessed could all
' be vastly different. I have run into this misapplication issue with other programs.
'
Const ns100_per_day = 864000000000# ' 86400 sec x 1.0e7 units of 100 ns per second
Const Base_Time_Offset = 109205# * ns100_per_day ' approx delta betw EFU and MS serial base times = 299 yrs x 365.2422699 dy/yr x 8.64e11 100ns/dy
EFU_to_MS_serial = (efu_time - Base_Time_Offset) / ns100_per_day - utc_correction
End Function