Sign in to follow this  
Followers 0
llewxam

Excel Date and Time

1 post in this topic

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

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  
Followers 0