Excel Date and Time

[llewxam]

OK, to be brutally honest here, I don't know who else may need this, but since I had to go through it I will gladly spare any other folks from having to! hmm, Why am I always getting stuck on date and time issues??

This time around, I was working on writing a SonicWall logfile analysis tool for a client who was exporting the logs as Excel sheets, and the way the timestamp was presented was as a number. After some Googling the method was found, and this UDF was born. Even if you don't ever NEED it, I find the way the timestamp is generated interesting, so at least you will hopefully enjoy that, too.

Example:

MsgBox(0, "TimeStamp", _ReturnExcelTimeStamp(40709.3859816667))

Returns "06/15/2011 09:15:49 (Wednesday)"

Please be sure to #Include <Date.au3>

Ian

Func _ReturnExcelTimeStamp($hTimeStamp)
    $iDays = StringSplit($hTimeStamp, ".")
    $hDateRaw = _DateAdd("d", $iDays[1], "1899/12/30")
    $hMonth = StringMid($hDateRaw, 6, 2)
    $hDay = StringMid($hDateRaw, 9, 2)
    $hYear = StringMid($hDateRaw, 1, 4)
    $hDate = $hMonth & "/" & $hDay & "/" & $hYear
    $hHours = StringTrimLeft($hTimeStamp, 5)
    $hHourCalc = 24 * $hHours
    $Where = StringInStr($hHourCalc, ".")
    $hDaysHour = StringFormat("%.2i", StringLeft($hHourCalc, $Where - 1))
    $hMinCalc = StringTrimLeft($hHourCalc, $Where - 1)
    $hDaysMinCalc = 60 * $hMinCalc
    $Where = StringInStr($hDaysMinCalc, ".")
    $hDaysMin = StringFormat("%.2i", StringLeft($hDaysMinCalc, $Where - 1))
    $hSecCalc = StringTrimLeft($hDaysMinCalc, $Where - 1)
    $hDaysSec = StringFormat("%.2i", Round(60 * $hSecCalc, 0))
    $hDayOfWeek = _DateDayOfWeek(_DateToDayOfWeek($hYear, $hMonth, $hDay))
    Return $hDate & " " & $hDaysHour & ":" & $hDaysMin & ":" & $hDaysSec & " (" & $hDayOfWeek & ")"
EndFunc   ;==>_ReturnExcelTimeStamp