Convert EPOCH Time to Date/Time in Excel

And now for something completely different. I was recently asked to retrieve SMS messages from an old iPhone. I was able to get to the backup of the SMS database easily enough, and it opened with SQL Lite, and I exported the messages to Excel. When I started looking at the messages in Excel, I noticed that the “date” field was a string of numbers that looked something like 1302180658.

After looking into this, I discovered that this was the Epoch Time (aka the UNIX Time). This is the number of seconds since January 1, 1970 00:00:00 UTC. I then spent a little time using Google to find out how to convert this to a real Date/Time. I found plenty of online tools to do so, and I even found a site that had a formula for Excel. When I used the formula from the web site, I got a date in the future, so I knew something was wrong.

I then broke down the formula into its components and discovered that the constant that was provided in the formula I got from the web site was wrong. Anyhow, enough back story. Here is how to convert Epoch Time (UTC) to Date/Time (UTC) in Excel:

Row A contains the Epoch Time (i.e., 1302180658)
Cell B2 contains the date January 1, 1970 (in a date formatted cell that is named - I called mine EPOCH)  This is a constant.
Row C contains the following formula: =sum(A2/86400)+EPOCH

Here is a visual

Hope this helps – please let me know if you have questions or issues with using this.  Thanks for stopping by!

More about the Motorola Froyo Update

I just got the details on the updated version of Android on Motorola devices that doesn’t work with Exchange Active Sync: version 2.2 Kernel version 2.6.32.9-g874c292 Build number FRG22D.

Again, if you are having trouble with this (things were working fine last week, and on Friday or Saturday EAS suddenly stopped working), please contact Motorola and your carrier and complain about this.

I would love to hear about your customer service experiences related to this.

Motorola Pushes an Android 2.2 (Froyo) Update that Breaks Active Sync

Three or Four days ago, Motorola pushed an update to Android 2.2 (Froyo) that broke Exchange Active Sync (EAS). If you have one of these devices and EAS is no longer working for you, you should contact Motorola and your carrier and let them know. In the interim, you can use Touchdown to continue the synchronization of your Exchange mail, calendar, and contacts.

Windows Phone 7 Released to Manufacturing

Just a quick note to let everyone know that the Windows Phone 7 product team has released the Windows Phone 7 OS to manufacturing. I hope it does well, but I fear that MS is too late to the dance.

Windows 7 WIN! Problem Steps Recorder (psr.exe)

The Coolest New Feature in Windows 7.  Problem Steps Recorder.

Have you ever been on the phone with your (insert the name of your computer challenged relative/friend here) who is asking you for help with a problem?  Have you ever wished you could see EXACTLY what this person is doing?  Well, with Windows 7 and Problem Steps Recorder, you can.

Ask the person you are supporting to click Start, type “psr.exe” (without the quotes) and press Enter.  In the Window that launches, ask your relative/friend to click “Start Record”.  Ask your relative/friend to reproduce the issue/error.  Once  your relative/friend has done this, ask your relative/friend to click “Stop Record”. 

Ask your relative/friend to give the file a meaningful name when asked, and ask your relative/friend to make a note where it is being saved.  Once this is done, ask your relative/friend to email you the ZIP file that was created.  When you receive the email, you will have a compiled HTML file with screen shots and a description of exactly what your relative/friend did to cause the error.  This file can be viewed in a web browser.   Following is a VERY simple session that I captured using this tool.  It looks a LOT better in all of its HTML glory.  All of the images can be clicked and viewed much larger.  And passwords are represented in the text log at the bottom with *******.

*******************************************************************************************************************************************************************************************************

Recorded Problem Steps

This file contains all the steps and information that was recorded to help you describe the problem to others.

Before sharing this file, you should verify the following:

•The steps below accurately describe the problem.
•There is no information below or on any screenshots that you do not want others to see.
Passwords or any other text you typed were not recorded, except for function and shortcut keys that you used.

There were errors during recording. Some information may be missing.

You can do the following:

•Review the recorded problem steps
•Review the recorded problem steps as a slide show
•Review the additional details

Problem Steps

Previous Next
Problem Step 1: (8/4/2010 10:34:10 AM) User left click on “Ribbon (property page)” in “Document1 – Microsoft Word”

Previous Next
Problem Step 2: (8/4/2010 10:34:12 AM) User keyboard input in “Document1 – Microsoft Word” [... Enter]

Previous Next
Problem Step 3: (8/4/2010 10:34:20 AM) User left click on “Google Search (editable text)” in “Google – Windows Internet Explorer”

Previous Next
Problem Step 4: (8/4/2010 10:34:21 AM) User keyboard input in “Google – Windows Internet Explorer” [...]

Previous Next
Problem Step 5: (8/4/2010 10:34:33 AM) User left click in “Document1 – Microsoft Word”

Previous Next
Problem Step 6: (8/4/2010 10:34:34 AM) User keyboard input in “Document1 – Microsoft Word” [... Shift-Backspace ...]

Return to top of page…

Additional Details

The following section contains the additional details that were recorded that can help find a solution for your problem.

These details help accurately identify the programs and UI you used while recording the problem steps.

This section may contain text that is internal to programs that only very advanced users or programmers may understand.

Please review these details to ensure that they do not contain any information that you would not like others to see.

Recording Session: 8/4/2010 10:33:58 AM – 10:34:52 AM

Problem Steps: 6, Missed Steps: 2, Other Errors: 0

Operating System: 7601.16562.amd64fre.win7sp1_beta.100603-1800 6.1.1.0.2.1

Problem Step 1: User left click on “Ribbon (property page)” in “Document1 – Microsoft Word”
Program: Microsoft Word, 14.0.4762.1000, Microsoft Corporation, WINWORD.EXE, WINWORD.EXE
UI Elements: Ribbon, NetUIHWND, NUIPane, Ribbon, MsoWorkPane, Ribbon, MsoCommandBar, MsoDockTop, MsoCommandBarDock, Document1 – Microsoft Word, OpusApp

Problem Step 2: User keyboard input in “Document1 – Microsoft Word” [... Enter]
Program: Microsoft Word, 14.0.4762.1000, Microsoft Corporation, WINWORD.EXE, WINWORD.EXE
UI Elements: Microsoft Word Document, _WwG, Document1, _WwB, _WwF, Document1 – Microsoft Word, OpusApp

Problem Step 3: User left click on “Google Search (editable text)” in “Google – Windows Internet Explorer”
Program: Internet Explorer, 8.00.7600.16385 (win7_rtm.090713-1255), Microsoft Corporation, IEXPLORE.EXE SCODEF:3544 CREDAT:72136, IEXPLORE.EXE
UI Elements: Google Search, Google, Internet Explorer_Server, Shell DocObject View, Google – Windows Internet Explorer, TabWindowClass, Frame Tab, Google – Windows Internet Explorer, IEFrame

Problem Step 4: User keyboard input in “Google – Windows Internet Explorer” [...]
Program: Internet Explorer, 8.00.7600.16385 (win7_rtm.090713-1255), Microsoft Corporation, IEXPLORE.EXE SCODEF:3544 CREDAT:72136, IEXPLORE.EXE
UI Elements: Internet Explorer_Server, Shell DocObject View, Google – Windows Internet Explorer, TabWindowClass, Frame Tab, Google – Windows Internet Explorer, IEFrame

Problem Step 5: User left click in “Document1 – Microsoft Word”
Program: Microsoft Word, 14.0.4762.1000, Microsoft Corporation, WINWORD.EXE, WINWORD.EXE
UI Elements: Microsoft Word Document, _WwG, Document1, _WwB, _WwF, Document1 – Microsoft Word, OpusApp

Problem Step 6: User keyboard input in “Document1 – Microsoft Word” [... Shift-Backspace ...]
Program: Microsoft Word, 14.0.4762.1000, Microsoft Corporation, WINWORD.EXE, WINWORD.EXE
UI Elements: Microsoft Word Document, _WwG, Document1, _WwB, _WwF, Document1 – Microsoft Word, OpusApp

Return to top of page…

Dell Streak – First Impressions

I have had about 30 minutes with the new Dell Streak, and I thought I would offer my initial impressions. First the good:

- It has a BIG beautiful screen
- It fits nicely in my pants pocket (it is thin)
- It has a nice BIG soft keyboard
- It is fast
- It runs the Android OS

And now the not so good:

- It has a proprietary connection for charging and synch
- The soft keyboard does not support the standard “hold the alpha key for the ALT symbol”
- Its support for Exchange is via WebDAV.  It won’t connect to my Exchange 2010 Server.
- It is running Android 1.6

Overall, I think it is very nice, and I think it will be much better with an OS upgrade (2.2 please) which should include full Exchange Active Sync support along with Calendar and Contacts.  I am hoping to get some more time with it in the next couple of days.

Two hours I will never get back

I decided to call Sprint customer support this morning and ask them to help me with Exchange Active Sync (EAS) on my Hero running Android 2.1. Before calling, I updated my Hero with the latest OS bits, and I tried to sync (ending up with the same error I have seen all along, which I detail in a post below). I then verified that my EAS policy was set so that it did not require a password.

I called Sprint and got a very nice tech support person (no names, because I do not fault these guys). He walked me through the email setup process on the device, resulting in the error. He then had my clear the local phone profile and essentially re-activate the phone on the Sprint network. Did that, and we tried email again. Same error. He then directed me to two different blog posts that outlined two different things that had been done by others to resolve the problem. I had already tried both, but I played along. One had me check the inherited permissions on my account in ADUC, and the other had me check the EAS policy settings in EMC. Neither fixed my issue. Same error. He then said he would like to connect me to HTC’s support line.

After waiting for a bit, I got a support tech from HTC (again, no names). He had me provide the device serial number, and he walked me through the email configuration process again. Same error. He then had me try to install the .CER files, using the default application that only sees .p12 files. When this failed, he suggested I download File Manager from the Market and browse to the SD card (where the .CER files are) and tap on them and try to install them that way. I did so, and when I tap the .CER file, I am presented with a box asking me which application I would like to use. Bottom line – I was still not able to install the .CER files.

The HTC tech then suggested that I download Touchdown from the Market. He said that it worked with EAS and that they were suggesting this for people having issues with the native Mail application.

I told him that I was disappointed in HTC for breaking EAS with the 2.1 update. I told him that I hoped that they would put out an update to fix this issue, like they did with the Evo. I told him that I was reluctant to pay an additional $20 (for Touchdown) when the phone is advertised as supporting “direct push for corporate email.”

If you have a Hero and can not get it to work with your Exchange Server (doing EAS), please call HTC support at 866-449-8358. Feel free to use the points I mention above to drive home this issue. I am going to try to get in touch with Google through work to see what they have to say about this.

More later.

Disappointed with Sprint

I am a long-time personal customer of Sprint (10+ years). I recently upgraded one of the 5 phones on my plan to the HTC Hero. With the update to Android 2.1, the Hero no longer syncs email with Exchange using Active Sync.

I am unable to find a place on the Sprint website where I can submit this as an issue (and I am well aware of the support forums). I am unable to get the Sprint website to show me my contract details.

And worst of all, Sprint is STILL selling the Hero and Moment – two devices that are now more than a month past end of life (they will not receive any further OS updates). This is shameful. When I first got the Hero, it was running Android 1.5, and EAS was working flawlessly. The upgrade to 2.1 broke that. And Sprint is not doing anything about it, other than selling more of the same broken devices.

The Kin One and Kin Two are dead

Microsoft announced that it is discontinuing the Kin One and Kin Two due to disappointing sales. They will continue to support current owners, but they will cease all development and updates. Verizon indicates that they have plenty in stock, in case you want to get one as a souvenir.

Evo Update fixes Exchange 2010 Active Sync issue

I just verified that the update to the Evo from 28 June 2010 fixed the issues with Exchange Active Sync and Exchange Server 2010. Great news for Evo users. I am still hoping Sprint/HTC/Google puts out a fix for the Hero.

Follow

Get every new post delivered to your Inbox.