Jump to content
Sign in to follow this  

Excel Date and Time

Recommended Posts


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.


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

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

Please be sure to #Include <Date.au3>


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

My projects:

  • IP Scanner - Multi-threaded ping tool to scan your available networks for used and available IP addresses, shows ping times, resolves IPs in to host names, and allows individual IPs to be pinged.
  • INFSniff - Great technicians tool - a tool which scans DriverPacks archives for INF files and parses out the HWIDs to a database file, and rapidly scans the local machine's HWIDs, searches the database for matches, and installs them.
  • PPK3 (Persistent Process Killer V3) - Another for the techs - suppress running processes that you need to keep away, helpful when fighting spyware/viruses.
  • Sync Tool - Folder sync tool with lots of real time information and several checking methods.
  • USMT Front End - Front End for Microsoft's User State Migration Tool, including all files needed for USMT 3.01 and 4.01, 32 bit and 64 bit versions.
  • Audit Tool - Computer audit tool to gather vital hardware, Windows, and Office information for IT managers and field techs. Capabilities include creating a customized site agent.
  • CSV Viewer - Displays CSV files with automatic column sizing and font selection. Lines can also be copied to the clipboard for data extraction.
  • MyDirStat - Lists number and size of files on a drive or specified path, allows for deletion within the app.
  • 2048 Game - My version of 2048, fun tile game.
  • Juice Lab - Ecigarette liquid making calculator.
  • Data Protector - Secure notes to save sensitive information.
  • VHD Footer - Add a footer to a forensic hard drive image to allow it to be mounted or used as a virtual machine hard drive.
  • Find in File - Searches files containing a specified phrase.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.