narrow default width wide
colour style colour style colour style colour style

VBA to convert UTC time to something friendly

I'm doing some account cleanup in a number of directories to prepare for a metadirectory implementation. AD has been as painful as anything to work with. Here's some code that you can use to convert the UTC time that AD likes to use to something a bit more usable. (I dumped everything into Access so I could write a bunch of queries against all of the directories)

I'm having it return 1/1/1601 if the record doesn't exist (i.e. for lastlogontimestamp, the user has never logged on). You could have it return whatever you wanted to though..

Function UTC2Normal(utcDate) As Date
    If utcDate "" Then
        intvartype = VarType(utcDate)
        'utcDate = CDbl(utcDate)
        intutcDate1 = utcDate - 1.16444736E+17
        intutcdate2 = intutcDate1 / 10000000
        intutcDate3 = intutcDate2 / 1440
        intutcdate4 = intutcDate3 / 60
        '148012 = days from 1601 til today (3/31/06)
        '13238 = days from 1970 til today (3/31/06)
        '"d" compare is MUCH faster
        'UTC2Normal = DateAdd("s", intutcdate2, "1970-01-01")
        UTC2Normal = DateAdd("d", intutcdate4, "1970-01-01")
        UTC2Normal = "1/1/1601"
    End If
End Function

Here's some sample SQL syntax to utilize it:

SELECT ADUsers.lastLogonTimestamp, utc2Normal(ADUsers.lastlogontimestamp) AS LastLogonFriendly, * FROM ADUsers;

If you don't care about seconds, just days, use the line in blue instead of the line in green. It is much faster.

To my everlasting shame, it took me about 4 hours to get this working correctly. I kept trying to get it to work using straight SQL but I'm not sure if it was even possible. After beating my head against the wall for way too long I stepped out for a smoke and my brain starting thinking about other options. Presto! Problem solved.