Excel formula to convert minutes into Hours and Minutes

On occasion I end up with an Excel spreadsheet that has a list of items, each of which has a duration. It’s easy to sum up the minutes and get a total number of minutes (see B7 in the picture below).

But it would be nice to see that in “x hours y minutes.” don’t you think? Yup, so do I. And that’s what I’ve done in the C9 cell in the picture below.

Screen snippet showing a column of minutes summed up, and then convert that into human readable hours and minutes
Human Readable Hours and Minutes

So without further ado, here is the Excel formula to do this

=ROUNDDOWN(B7/60,0)&" hours "&MOD(B7,60)&" minutes."

Let me know if you find this helpful.

Editing Microsoft Office Documents on the iPad

I got asked on the weekend about what do I use on the iPad to edit Microsoft documents.

I’ve been using an iPad (the ‘new iPad’ or the ‘iPad 3’ depending on who you talk to) and have been needing to both create and edit Microsoft Office Word and Excel documents.

I’ve tried using Google Apps but have found that it just wasn’t that easy, and I had to be online, which wasn’t a problem most of the time, but occasionally was quite frustrating if the connection was either slow or non-existent.

So I hunted around for a native app, and ended up with Quick Office HD, which is perhaps the most expensive app I’ve bought. However, it has proven to be very well spent money.

I’ve been able to open existing files, create new ones, save them to a Dropbox folder and either have them available on my workstation or laptop, or email a colleague a link to the file. This has proved to be a great combination (Quick Office HD and Dropbox).

As it so happens, Quick Office has been acquired by Google, so I’m hopeful that sometime in the near future a better option will exist for Google Doc’s.

What Version of Microsoft Installer?

Sage ACT! 2011 and 2012 require Microsoft Installer 4.5 or better to be installed. I like to know prior to running the Sage ACT! installer just which version is installed as the Sage ACT! installer will install it if it’s required and then do a reboot. And we all know that reboots are often not welcome.

Thus for one an all’s installation enjoyment, here is the code for determining just which version of the Microsoft Installer is present.

@Echo off
:: Created by Ben Hamilton ACT! CRM Certified Consultant
:: to display which version of the Microsoft Installer is installed.
:: http://ben.hamilton.id.au
Echo set args = WScript.Arguments >fvi.vbs
Echo Set fso = CreateObject("Scripting.FileSystemObject") >>fvi.vbs
Echo WScript.Echo fso.GetFileVersion(args(0)) >>fvi.vbs
Echo Wscript.Quit >>fvi.vbs
for /f "skip=2 " %%G IN ('cscript fvi.vbs %systemroot%\system32\msi.dll') DO (Set MSIVersion=%%G)
Echo Microsoft Installer %MSIVersion:~0,3% is installed.
del fvi.vbs /q
pause

Hope you find it as useful as I do.

No IMAP to Exchange 2003

So I ran into an issue I’ve not seen before yesterday. Couldn’t get Microsoft Outlook 2010 configured to talk via IMAP to Microsoft Exchange 2003, although the username and password was correct, it just would not connect. Turns out the answer is in this Microsoft KB article: Users Cannot Log On Using POP3 and the Error 0x80040920 Is Logged. Although about POP3 and not IMAP, the fix is the same, make both the account name and the alias in Microsoft Exchange the same.

Update on XPUnlimited

So some time ago I wrote a post about using XPUnlimited questioning it’s legality.

Today I’ve had another systems engineer tell me that it is legal. So I’ve spoken with the local distributor (http://xpunlimited.com.au/) here in Australia and asked them to clarify this.

Part of their reply pointed me to the FAQ on the XPUnlimited.com site:

It is legal to use XPUnlimited on top of Windows. If you want to know the details, read the license terms of Windows.

Certainly looking through the links and reading the paragraphs they cite makes me somewhat more comfortable about hearing of people who use it.

Export ACT! reports to Excel

Had a client today need some information out of ACT!, and ideally it would be in an Microsoft Excel spread sheet.

Given that one of the default ACT! reports gave the bare minimum required (Group Membership) we spent some considerable time editing a copy of that report to display the actual fields we wanted, both contact fields and group fields. What we wanted was a spread sheet that showed each contact in a particular groups sub-groups, detailing which sub-group each contact was a member of, and also showing some groups specific information for each.

This was all fine except that it still wasn’t in a spread sheet once we produced the report, printing it, out to PDF, were no problem, but no .xls file.

This is where KB 14690 came in handy. It details how to modify the Windows Registry such that the output options for ACT! reports will then include such welcome and handy options like Microsoft Excel, Microsoft Word, TIFF file and Paged html.

Now when we produce the report, we get a beautiful Excel spread sheet with just the information the client requested.

Hope you find that useful, and if you do, let me know how you’ve applied it.

Who should pay for Microsoft SQL installations that go wrong?

ah, SQL humorThe day is almost over, so I’ll fill you in on some of what occurred today, as it relates directly to this post by Mike at GLComputing. This was originally written as a comment to his post and kinda grew, so I’m posting it here in full.

SQL 2008 Express R2 has finally let me install a default SQLEXPRESS instance but I still can’t get a custom instance of ACT7 working. Who should be paying for the time it’s taking me to work out this issue?

I don’t yet have a complete answer to that question.

Imagine for a moment that I buy a brand new car, from a dealership. The car has trouble starting. So I go back to the dealership and ask them to fix it. They do so. I as a client go home happy.

But what’s really occurred? Well, the dealership gets a mechanic to look at it; he determines that a component if faulty, he replaces it with one off the spare part shelf in the dealership.

The dealership doesn’t want to wear the cost of the mechanics time or the cost of the part, so they put in a warranty claim to the manufacturer for the time taken by the mechanic and the cost of the part.

The manufacturer pays up, occasionally they audit the dealership to make sure fraud isn’t occurring. The manufacturer actually obtained the faulty component from a supplier. They then make a claim to the supplier for the costs.

The supplier pays up.

I know this because many years ago (early ’90’s) I was a Warranty Manager for a car dealership.

Should the same process apply in the software industry?

Is it the fault of the reseller/dealership that software/component failed?

Is it the fault of the software house/manufacturer that the software/component failed?

Is it the fault of the supplier that the software/component failed?

Of course the initial supplier will argue that they make their product to stringent standards, and they can’t account for all the possible variables of other hardware and environmental conditions.

And of course the software house/manufacturer will say they took all appropriate steps.

And also, of course, the reseller/dealership will say they aren’t to blame either.

And the customer, well, the customer is never wrong, right?

Getting a little more specific, in my case today, the client has all good name brand equipment, setup by a reputable IT firm with a solid reputation. I couldn’t fault either their spec’s or configuration. I know the amount of effort I’ve put into this today. I know how much effort I’ve put into making SQL installs go smoothly, to the point where they mostly do go smooth for me, but sometimes, like today, they go very very wrong.

And so yes, I lay the fault with the remaining two players. Me, the guy on the pointy end of the issue, who gets to look like an idiot in front of a customer because he can’t make shrink wrapped software work on name brand computers. [Do you think I’m a little cranky? hint, I am.]

You see, the vendor here (Sage) have a product they sell (Sage ACT!) that uses a database in the back end (Microsoft SQL Server 2008 Express R2). The vendor (Sage) has chosen to use that product, use that version of the product. That choice means an implicit responsibility to issues using their product with the 3rd party product. Let me say it clearly, “Sage, you chose to use Microsoft SQL 2008 Express R2, which makes you half responsible.”

The 3rd party, the supplier of that component, Microsoft, have chosen to update their product, and sometimes that transition hasn’t gone smoothly for them, but nonetheless they have updated on a semi-regular basis (although not yearly, and the topic of frequency will be the subject of another discussion). They provide help via their KB articles and revert to the line “too many other environmental factors, not our problem”.

Reminds me of a joke I heard years ago:

A helicopter was flying around above Seattle when an electrical malfunction disabled all of the aircraft’s electronic navigation and communications equipment. Due to the clouds and haze, the pilot could not determine the helicopter’s position and course to fly to the airport. The pilot saw a tall building, flew toward it, circled, drew a handwritten sign, and held it in the helicopter’s window. The pilot’s sign said “WHERE AM I?” in large letters. People in the tall building quickly responded to the aircraft, drew a large sign and held it in a building window. Their sign read: “YOU ARE IN A HELICOPTER.” The pilot smiled, waved, looked at her map, determined the course to steer to SEATAC airport, and landed safely. After they were on the ground, the co-pilot asked the pilot how the “YOU ARE IN A HELICOPTER” sign helped determine their position. The pilot responded “I knew that had to be the Microsoft building because, like their technical support, online help and product documentation, the response they gave me was technically correct, but completely useless.” – Thanks to Alun for the source link.

In my view, if a car had, say a throttle problem, the manufacture would be sorting out the problem quick smart. Sage, you need to compensate the people in the front line, in the trenches. Go hit Microsoft up if you’re not happy about it. That’s what the car manufacturers do. It works for them so don’t tell me it can’t work.

But, Sage, I’m not hearing much from you. And that’s concerning to me because in today’s world, the world of 2011, the internet, social media and a with your own social media presence, to not hear much at all is to hear all the other dissenting voices, to hear the competition.

Now honestly, the competition to Sage ACT! is woeful. Seriously, it is. ACT! is a great product, it’s flexible, customisable, and at least 11 other herbs and spices, all of which are pure goodness (honestly, it’s a lot more than 11). So here’s a hint to the competition, if you want a good CRM product, imitate ACT!.

Want to know one of its weaknesses? It’s reliance on product that doesn’t install properly. Achilles only had one heel that gave him trouble. Most, but not all, of the competition have figured this out, and allow either multiple backend databases to be used (MSSQL/MySQL/Oracle/PostgreSQL and others).

Now if you agree with me, let me know, if you don’t let me know as well, because as those that know me can attest, if you can prove your point, I’ll change. Of course, if you don’t give two hoots, then I guess I won’t be hearing from you. Either way, I’m gonna go hold my teddy bear and sing myself to sleep, hoping that the SQL install nightmare doesn’t plague me tonight. I’m gonna need some sleep to go fight this dragon again.

How to clear the MS Exchange mail queue quickly

Spent the day dealing with an exchange server that had been compromised. As a result, heaps of spam emails were in the exchange mail queue. Manually removing them is a major pain in the rear. Fortunately, others have shared how to clear the Microsoft Exchange mail queue of thousands for spam mails.

  1. Stop the SMTP service.
  2. Create a new spam folder for example in
    C:\Program Files\Exchsrvr\Mailroot\vsi 1\Spam
  3. With the SMTP service still stopped, move all the messages from the
    C:\Program Files\Exchsrvr\Mailroot\vsi 1\Queue
    to the spam folder (in case you need to retrieve a message)
  4. Restart the SMTP service.

Of course prior to doing that, the instructions at this Microsoft KB How to block open SMTP relaying and clean up Exchange Server SMTP queues in Windows Small Business Server is quite helpful in showing how to make sure your exchange server is not an open relay. It also shows how to test if it is an authenticated relay attack that is the problem.

How to block open SMTP relaying and clean up Exchange Server SMTP queues in Windows Small Business Server

Is using XP Unlimted legal?

This is a question I’ve been asked, and I’ve seen a multitude of opinions out there on the wild wild web.

Thus I thought I’d share my findings.

First of all, what is XP Unlimited?

XP Unlimited turns your Windows System into a full blown Terminal Server, without any limit. XP Unlimited gives you eXtra Productivity when using Microsoft Windows.

Source: http://www.xpunlimited.com/

It is meant to turn your XP machine into the equivilant of a terminal server machine without the expense of spending money on Terminal Server CALs (Client Access Licenses)

Thus the question, is this legal? is it legit?

The document on the Microsoft Site (MS Word format) found here:
http://download.microsoft.com/download/6/9/5/695ba00d-c790-4c90-813a-f10539d97991/Licensing%20Windows%20Server%202008%20Terminal%20Services.doc
(Full URL included for the sceptics).

Now this document is about Windows Server 2008, but it makes three points that I feel are important:

  1. Is this a change from Windows Server 2003 licensing? No.
  2. Do I need a TS CAL if I am using a third-party technology like Citrix XenApp, Ericom PowerTerm WebConnect, Quest Virtual Access Suite, GraphOn Go-Global, etc. to directly or indirectly access the server software to interact with the graphical user interface? Yes.
  3. Note: Windows® client operating system (OS) license terms do not permit multiple users to access or otherwise use more than one licensed copy of the software simultaneously.

I would put XP Unlimited into this same category as these products.

So if you were to use XP Unlimited on an XP Pro machine, I would feel that invokes the third point above, if you where to use XP Unlimited on a Server 2003 machine then you would still require TS CALs, so I wouldn’t need XP Unlimited.

What do you think?

(updated: added info on what XP Unlimted is)

How to: Clear Outlook Location list

Had an issue yesterday where we wanted to remove some entires from Outlooks location list.

Huh? When you book an appointment in Microsoft Outlooks calendar you can specify a location. If ACT! by Sage has a Resource that is designated as a location, when ACT! sync’s with Outlook that location list gets filled in.

So, we wanted to edit that list in Outlook. Well, you can’t.

But you can clear the list completely, which for our purpose suited us fine, it’ll get repopulated with the correct values.

Thus, without further ado, here is how you do this:

Open up Regedit and remove the value from this key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Outlook\Preferences\LocationMRU

Note that you will need to replace the version number for your version of Microsoft Office (14.0 = MSO2010, 12.0 = MSO2007).

Hat tip to superuser.com.

How to: discover open files on Microsoft Server

So you’re working on a Microsoft Server machine, merrily getting stuff done, when you get a message saying that it can’t continue because files are open. Sound familiar? It happens to me often.

Here is how I find out which files are open and who has them open:

If you’re working on a Microsoft Server 2008,
Start | Administrative Tools | Share and Storage Management | Manage Open Files…

If you’re working on a Microsoft Server 2003,
Start | right click My Computer | Manage | Computer management (local) | System Tools | Shared Folders | Open Files

Makes it a great deal easier, now you know which file needs closing, and who you need ask to do it.

Hope that helps you. Anything you’ve found helpful? Let me know in the comments.

Command line terminal server

I’ve just come across a terminal server that wouldn’t let me log on. Turns out the maximum number of users the terminal server is licensed for has been exceeded.

Thusly, from another machine on the network, we open a command prompt and type
qwinsta /server:<servername>

This will give a list of the terminal server sessions on that server, note the ID number (aka sessionid) as we use them in the following commands.

We can reset a session by using
rwinsta <sessionid> /server:<servername>

or we can disconnect the session with
tsdiscon <sessionid> /server:<servername>

or we can logoff a session with
logoff <sessionid> /server:<servername>

Dan Rigsby has more info as does Scott Forsyth.

When SharePoint barfs

A client has a SharePoint installation that has died, with all their project files and data in it.

In the course of searching for how to resurrect it, I’ve found the following that seem to be things that others may well benefit from:

Possibly the biggest lesson here is to ensure that if your using SharePoint, make sure you back it up PROPERLY, ALL OF IT.

Evolving the ACT

It is now two weeks in to my new digs at Evolution Marketing – and it sure is fun.

Primarily I’ve been learning how to drive ACT!, software to take care of your contact management. Yesterday and today I’ve been playing with custom tables in the ACT! MS-SQL database. While ACT! looks after contacts and tasks, meetings & calls to do with your contacts really well, some clients would like to be able to do even more, like handle job sheets etc. Which is the reason for the custom tables. These allow us to track all sorts of other information and connect them to contacts & companies.

I’ve also setup a Blackberry Bold simulator and a Windows Mobile 6 Emulator, this allows me to experience the same things as our clients, notably HandHeldContact, which allows you to sync multiple calenders, notes, histories etc to your phone – loads more than MS Exchange alone allows.

To paraphrase the paragraphs above, I’m having fun learning new stuff!

The other staff at Evolution are great to work with, the coffee is good and all the tech toys I’ve asked for have been delivered, with the exception of the telephone headset, but it is on the way so they’re forgiven ;-). Woot!

Update 20th Oct: Got the telephone headset on Friday afternoon. Cool.