I created this System Uptime / Downtime / Availability Calculator Template as an easy way to work out the effect of any outage on our Service Level Agreements. This template is using Australian sites & timezones as this was what I needed, but the logic is pretty clear & you can easily adapt it to suit your requirements. All times are calculated in WST, so please be sure to input your outage start & end times in WST. Leave me a comment if you have found this useful when managing your SLA’s.
Basically, the intention here is to count the number of business hours (ignoring weekends) that any outage affected multiple sites with different opening & closing times.
You will need to load the Analysis Toolpak to Excel to utilise the NetworkDays function listed below: http://office.microsoft.com/en-au/excel-help/load-the-analysis-toolpak-HP001127724.aspx
From this example, you can see that the a two hour outage occurring between 6 and 8am WST has no impact on the Perth office, as the outage occurred before the start of business in that office. Adelaide recorded a lower outage duration, as they are half an hour behind EST and therefore were only affected for 1.5 hours:
The SpreadSheet Explained
When a time & date are entered into the top fields, the times are calculated into three sections underneath: The corresponding times in Adelaide, Brisbane and EST. Logic is then applied to these converted values in order to ensure that any input values fall within the specified 8am to 5pm range. Without this validation a 5pm WST outage will clock in as 7pm Brisbane time (for example) and the formula will calculate a higher Downtime, as it counts the time overnight as part of the Outage
To keep the formulas modular, the variables are specfied in this section & used as references within the rest of the spreadsheet. This allows us to easily change the time difference to Melbourne / Sydney when the clocks change, as well as the number of hours per working week, and the opening / closing times we are measuring against:
StartTime = Office’s opening time
EndTime = Office’s closing time
Converted Start Time: If StartTime earlier than OpenTime, then OpenTime. If StartTime later than CloseTime, then CloseTime. Otherwise, the input value is used.
Converted End Time: If EndTime later than CloseTime, then CloseTime. If EndTime earlier than StartTime, then StartTime. Otherwise, the input value is used.
Actual End Time: The WST end time of the incident, plus or minus the time difference specified in the Variables column
The main calculation is using Excel’s ‘NetworkDays’ function to ignore weekends, and uses the DayEnd & DayStart times input into the spreadsheet to calculate the actual business hours each outage affected.
The Users section is not used in any formulas above, however it is intended to make it easier to calculate the true impact of any outage. For example, a 2 hour outage will incur a 1.3% downtime (98.7% uptime) for all sites, assuming the outage falls within the open & close times of all sites. However if only one site (29% of users) was affected by the outage the true downtime would be:
Users (29%) x Downtime (1.3%) = 0.38% actual downtime (99.62% uptime) for the entire organization over the course of the month.
The user-specified fields are in orange, and roll up into the total via AutoSum at the top. The AutoSum value is then used as 100% of all users, which is then broken down to the site percentage of the total.
Download Uptime Calculator.xlsx (21kb):