Excel – Converting From UNIX Time

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.

  1. First I used =DATEVALUE("01/01/1970") in order to get the number representation of the first day in UNIX time (January 1, 1970).
  2. 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.
  3. Finally I ended up with the following formula in order to convert the UNIX time stored in cell A2 into a text representation: =TEXT(A2/86400+25569,"YYYY-MM-DD HH:MM:SS")

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: =TEXT([UNIX_TIME_CELL]/86400+25569-4/24,"YYYY-MM-DD HH:MM:SS").

From Office 2003 To Office 2007 or Office 2010

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.

VBA Snippet – GetTimezoneOffset()

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
  ie.Navigate "about:blank"
  While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
  ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
  GetTimezoneOffset = ie.Document.parentWindow.n
End Function

Of course, there are downsides to this solution. The first (being minor) is the fact that you have to include a reference by going to Tools > References… and then selecting “Microsoft Internet Controls”. The second is the fact that every time this function is called, an instance of Internet Explorer is created and then quickly (speed depending on the computer) destroyed. Other than that, the function will work. Its purpose is to return the amount of minutes behind GMT (or UTC) time the computer is. This is the equivalent of doing the following in JavaScript:

var offset = (new Date).getTimezoneOffset();