Today I was working on a one-time HIVE report which due to the amount of data that is in each table, took a significant amount of time to run. After running though, I had the data that I wanted except for the fact that I neglected to convert the UNIX timestamp into a text version of the date. Instead of re-running the query again, I decided to use a formula in order to convert the UNIX time into a meaningful string.
- First I used
=DATEVALUE("01/01/1970") in order to get the number representation of the first day in UNIX time (January 1, 1970).
- Next I determined that UNIX time must be divided by the amount of seconds in a day (86400) in order to be in accordance with the way Excel converts dates to numbers.
- Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation:
It is important that there is no built-in way to determine the timezone offset of the current users computer with a function. On the other hand, if you already know the timezone offset, you could include this in your calculations. For instance, if the data was captured in a timezone of -0400, you could use the following formula:
Did you know about the interactive guides provided by Microsoft to help Office 2003 users to migrate to the corresponding 2007 or 2010 software? If you are trying to get use to Office 2010, you can use the interactive guides on this page. If you are trying to get use to Office 2007, you can use the interactive guides listed below (or go here to download them):
I realize that this is a very late post, but I figured it is still worth mentioning for all of those who remember how to do something in the old version of Excel, Word, etc. but not in the newer version. Click here for all of the information from an official Microsoft site.
One of the things I think is amazing about VBA is the fact that it is so incomplete in some ways. For instance, did you know that there is no native way to get the local timezone offset. Yesterday I was working on a VBA SOAP plugin for Excel and realized this horrible fact. Of course, my first instinct was to Google the answer. Unfortunately, not even Google could provide a satisfactory (meaning short) solution. Therefore, I came up with the following VBA function:
Public Function GetTimezoneOffset() As Integer
Dim ie As New InternetExplorer
ie.Visible = False
While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
GetTimezoneOffset = ie.Document.parentWindow.n
var offset = (new Date).getTimezoneOffset();