Serial Format
This format represents time in a Double type variable. The integer portion (to the left of the decimal point) represents the number of days since 30-December-1899 (1 = 31-December-1899, 2 = 1-January-1900,...39075 = 23-December-2006)., and the fractional portion of the value (to the right of the decimal point) represents a fraction of a 24-hour day (0.25 = 6:00:00, 0.5 = 12:00:00, 0.75 = 18:00:00).  This is the format used in VB/VBA's time-related functions.

FILETIME
The FILETIME is a Type comprised of two 32-bit Long integers, which together form a 64-bit integer (not supported in VB/VBA).  The value of the FILETIME is the number of 100-nanosecond intervals since 1-January-1601.  The system uses FILETIME structures to store the date attributes of a file. These dates and times are stored as GMT values. The FILETIME Type is shown below:

Public Type FILETIME
        dwLowDateTime As Long
        dwHighDateTime As Long
End Type

SYSTEMTIME
The SYSTEMTIME is a Type with an element for each component of a date and time (i.e.,  year, month, day, hour, minute, second). The SYSTEMTIME Type is shown below:

Public Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type

There is also an obsolete time format called DosDateTime, which is not discussed here. See this MSDN article form information about the DosDateTime format. For a discussion of file times in general, see this MSDN article.

You can download a bas code module or a complete workbook that contains the following functions.

Serial Time Functions:
----------------------
GMTTimeToLocalTimeSerial
LocalTimeToGMTTimeSerial

FILETIME Functions:
-------------------
FileTimeToSerialTime
SerialTimeToFileTime

SystemTime Functions:
---------------------
SystemTimeToSerialTime
SerialTimeToSystemTime

Local Time Functions
--------------------
LocalTimeNowAsSerial
LocalTimeNowAsFILETIME
LocalTimeNowAsSYSTEMTIME

GMT Functions:
--------------
GMTNowAsSerial
GMTNowAsFILETIME
GMTNowAsSystemTime

TimeZone And DST Functions:
---------------------------
CurrentTimeZoneName
IsDateWithinDST

Functions To Be Called From Worksheet Cells:
--------------------------------------------
CreatedFileDateTime
AccessedFileDateTime
ModifiedFileDateTime
CurrentTimeZoneName
IsCurrentlyDaylightTime
GMTBias
ExcelFileNameReferenceToFileName

The first pair of functions convert between Local Time and GMT. Daylight Savings Time is taken into account. The rules for Daylight Savings Time in these function are the USA rules. Other countries may have other rules. You can adapt the code to match your local rules. In the USA, for years before 2007, Daylight Savings Time begins on the first Sunday in April and Standard Time begins on last Sunday in October. For years 2007 and later, Daylight Savings Time begins on the second Sunday in March, and Standard Time begins on the first Sunday of November.

The downloadable module contains a procedure call IsDateWithinDST which returns True or False indicating whether the specified date is within the Daylight Saving Time period.

To determine the GMT of a Local Time (and the reverse) the GetTimeZoneInformation Windows API function is used. It fills a TIME_ZONE_INFORMATION Type with information about the current time zone, its offset from GMT, and information about Daylight Savings Time. The TIME_ZONE_INFORMATION  Type is show below:

Public Type TIME_ZONE_INFORMATION
        Bias As Long
        StandardName(0 To 31) As Integer
        StandardDate As SYSTEMTIME
        StandardBias As Long
        DaylightName(0 To 31) As Integer
        DaylightDate As SYSTEMTIME
        DaylightBias As Long
End Type

NOTE: If you are using the Windows API Text Viewer program, you will need to change the declaration of StandardName and DaylightName form StandardName(32) As Integer to StandardName(0 to 31) As Integer, and DaylightName(32)As Integer to DaylightName(0 to 31) As Integer, as is shown above. The declarations in the API Text Viewer are incorrect.

The GetTimeZoneInformation Windows API function populates the element of this Type. Here, Bias is the number of minutes that are added to the local time to get GMT. In the USA Central Time Zone, this value is 360 minutes, or 6 hours.  Conversion is based on the following two relationships:

GMT = LocalTime + Bias 
LocalTime = GMT - Bias

These relationships do not reflect Daylight Savings Time, since GMT does not change with Daylight Time. To accomodate Daylight Time,

GMT = LocalTime + Bias - IsDateWithinDST(LocalTime)
LocalTime = GMT - Bias + IsDateWithinDST(LocalTime)

The IsDateWithinDST function is included in the download module.

The Type stores the StandardName and DaylightName as an array of integers. To convert these arrays to strings, use the code below:

Function ConvertTimeZoneName(C() As Integer) As String
    Dim N As Long
    Dim S As String
    For N = LBound(C) To UBound(C)
        S = S & Chr(C(N))
    Next N
    ConvertTimeZoneName = S
End Function

You can the call this function with code like

Dim TZI As TIME_ZONE_INFORMATION
GetTimeZoneInformation TZI
Debug.Print ConvertTimeZoneName(TZI.DaylightName)

The ConvertTimeZoneName function is included in the downloadable module.  The function CurrentTimeZoneName will return a string containing the name of the current time zone.

The downloadable workbook also contains the following functions specifically designed to be called from worksheet cells, in a module named modWorksheetFunctions.

CreatedFileDateTime:

	Public Function CreatedFileDateTime(Optional FileName As String = vbNullString, _
        	        Optional TimeAsGMT As Boolean = False) As Variant

	Public Function AccessedFileDateTime(Optional FileName As String = vbNullString, _
        	        Optional TimeAsGMT As Boolean = False) As Variant

	Public Function ModifiedFileDateTime(Optional FileName As String = vbNullString, _
        	        Optional TimeAsGMT As Boolean = False) As Variant

	Public Function CurrentTimeZoneName() As String

	Public Function IsCurrentlyDaylightTime() As Boolean

	Public Function GMTBias() As Long

	GMTTime = LocalTime + TimeSerial(0, Bias, 0)
	LocalTime = GMTTime - TimeSerial(0, Bias, 0)

Public Declare Function SystemTimeToFileTime Lib "kernel32" ( _
    lpSystemTime As SYSTEMTIME, _
    lpFileTime As FILETIME) As Long

Public Declare Function FileTimeToSystemTime Lib "kernel32" ( _
    lpFileTime As FILETIME, _
    lpSystemTime As SYSTEMTIME) As Long

Public Declare Function FileTimeToLocalFileTime Lib "kernel32" ( _
    lpFileTime As FILETIME, _
    lpLocalFileTime As FILETIME) As Long

Public Declare Function LocalFileTimeToFileTime Lib "kernel32" ( _
    lpLocalFileTime As FILETIME, _
    lpFileTime As FILETIME) As Long

Public Declare Function SystemTimeToFileTime Lib "kernel32" ( _
    lpSystemTime As SYSTEMTIME, _
    lpFileTime As FILETIME) As Long

Public Declare Function FileTimeToSystemTime Lib "kernel32" ( _
    lpFileTime As FILETIME, _
    lpSystemTime As SYSTEMTIME) As Long

Public Declare Function GetTimeZoneInformation Lib "kernel32" ( _
    lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long