Track software licenses and IT support with Excel date functions

In my previous articles, I described how to use Microsoft Excel pivot tables to analyze support contracts and massage the data to help understand service contracts and potentially reduce support costs. In addition, there are other tricks for analyzing and reporting data. Let's explore the use of functions to calculate date differences.

Microsoft explains how to use Excel data functions to calculate the number of months between two dates, which can be just as useful to IT as to end users.

A typical scenario where date functions would be helpful would be in analyzing hardware or software support data. Administrators can also use Excel to track warranties and support end dates. I have worked with many organizations that have significant quantities of hardware out of support or expired software licenses because it's hard to track them, and small to midsize businesses often can't afford dedicated tools.

Keeping desktop hardware and software current might seem to cost more, but it can result in cost savings. Calling in support on an out-of-support device not only delays the repair, but it also usually costs more because you miss the economy of scale when it is part of a large contract. Some support vendors will add the device to the contract at the next true-up but will charge time and materials for the current incident.

Table 1

With a little experience in setting up some Excel functions, it is easy to track dates. In the example from a support contract shown in Table 1 each hardware device is listed on a row with support details in the columns.

• Column A: Serial number of the device
• Column B: Warranty start date (provided by the OEM)
• Column C: Warranty end date (provided by the OEM). Note that if only the start date is provided, make sure the value in column B is in Excel DATE format. Simply put the following formula in column C:

=B2+(3*365) where "3" is the number of years of the warranty.

Columns D to F show the following for the year 2014:

• D: Monthly nonwarranty support cost -- this is what support will be after warranty expires
• E: Number of months that will be out of warranty for 2014
• F: 2014 annual support cost, which is the support cost after warranty expires (Col D  * Col E)

Columns G-I and J-L are for the years 2015 and 2016, respectively.

Thus, with this table, you can see how many months each device will be out of warranty for the year and what the support cost will be for the year. Using a pivot table as described in my previous article could show the devices that expire in 2014, 2015, etc.

Next, we'll look at how to use Excel's Year and Month functions, as well as the IF statement, to determine if a warranty is out of date, for instance. Excel may be ubiquitous for finance, but IT admins can save money and hassles by knowing how to use it rather than turn to third-party software.