Converting Unix Epoch to Date in Excel

I ran into an interesting problem earlier today. I was using a CDR dump from our Cisco system. However, all of the datetime fields were represented as seconds. I knew right away that it used an epoch value. What I didn’t know is what the originating date was. Apparently, I was correct at my first guess. I guessed that it was using the standard Unix epoch value of 1/1/1970. Many applications, such as Microsoft Excel, use 1/1/1900.

Since I wanted to represent the correct date and time in Excel, I began looking around for a formula. To my surprise, nothing exists out of the box (unless my eyes are failing me). So, I had two options. The first option I headed down had me save my spreadsheet as a macro-enabled workbook. From there, I added a formula to an empty module.

   1: Public Function EpochConversion(timeZoneOffset As Long, myNumber As Long, myDate As Date) As Date
   2:     EpochConversion = DateAdd("s", myNumber + (timeZoneOffset * 3600), myDate)
   3: End Function

The function accepts 3 parameters: timeZoneOffset which is the offset from GMT (for example, New York City is in the Eastern Time Zone which is –5), the number of seconds, and the base date (in the event I need to use 1/1/1900 instead). So, an example use would look like this:

image

That works, but it requires that my spreadsheet is a macro-enabled workbook. I can accomplish something similar by using the following formula. This is my second option for handling the conversion.

=(((G2-(5*3600))/86400)+25569)

In this formula, the following values are used:


G2 The cell containing the number of seconds from epoch.
- If you are in the western hemisphere, this would be negative. Otherwise, change this to a plus sign. This handles the offset from GMT.
5 The is the value of the timezone from GMT. It’s combined with the minus sign above.
3600 The number of seconds in an hour used to assist with the timezone offset.
86400 The number of seconds in a day.
25569 The number of days since 1/1/1900 to assist Excel in the proper epoch conversion of 1/1/1970.

In either case, I’ll obtain the same result.


One thing is for sure, I’m hoping that I’m retired by January 19th, 2038 so that we don’t have another Y2K crisis when the 32-bit versions of epoch have overflows: http://2038bug.com/.

Office Ergonomics – Carrying Laptops and Work

Last night, August 7th, on World News with Diane Sawyer, their research team collected evidence to show how carrying purses and other items can cause harm if too heavy. According to their research, a single shoulder strap bag, such as a purse, messenger bag, duffle bag, should not weigh more than 5% of your body weight on a consistent basis. So, if you’re a male weighing in at 200lbs, your bag shouldn’t be more than 10lbs. I wanted to see what that meant for me. I currently carry a 17” Dell Precision M6700 that weighs in at 7.76lbs. That’s hefty, but it’s something that I feel is needed for the line of work I use. I’ve been using a backpack, but I’ll cover that in a bit. For this example, I’ll use the weight of the new messenger bag that I purchased for me and my team. Over on Amazon, we had purchased the 17.3” Microsoft Impact Messenger Bag for $43. Amazon states that the shipping weight is 2.9lbs. I didn’t weigh it, but the bags I purchased with shipping packaging weighed on average 3.5lbs. So, I’ll assume this bag weighs in at 2.9lbs. That’s already 10.66lbs before adding pens, paper, tablets, and anything else I may normally carry in my bag.

The more I thought about this, the more I started to think that I was an exception, right? So I looked around the office at some others that have laptops. Let’s assume another office worker has an ultra-light 13” Lenovo Yoga. According to their site, this comes in at 3.3lbs. Again, visiting Amazon, I came across a $17 Case Logic laptop bag/sleeve that weighs in at 14.4 ounces. So, at this point, we’re up to 4.2lbs. If that was all we were carrying, we’d likely be fine unless we weighed in less than 84lbs. However, once we start adding in a notebook, writing utensils, and a few other items, you’ll soon find that even going ultra-light, we can push the weight of our bag to the limit.

To solve this problem, we can always look to use lighter equipment or rolling bags minimizing the amount of carrying we do. We can also switch over to backpacks which are recommended for heavier equipment. Patch.com estimates that back packs should not exceed 10% to 15% of your body weight. For the office crowd, a 17” SwissGear ScanSmart Backpack is ideal.

Blogging for 10 Years and Counting

I’ve kept track of tweets. I’ve kept track of the user group anniversary date. I’ve kept track of my marriage anniversary date. But, for some reason I forgot about when I started blogging. That was, until one of my good friends, G. Andrew Duthie, blogged about forgetting about his blogging anniversary as well.

Like Andrew, my first blog was also up on the ASP.net blog site. Over the years I have compiled 570 posts on that site (interesting coincidence since our area code is 570) before switching to my personal site. I’ve posted (technology only) a total of 632 times over a 10 year span. I kept several different blogs in several different locations. I’ve since consolidated everything into one location.

Before I end this post, let’s reminisce:

image