I need help calculating EXCEL SLA for Server Downtime

I need someone to assist me with the following excel document. This document has two tabs. The first tab is the raw data for the excel sheet. The second tab is information that requires interpretation to get numbers. For instance, the Incidents By BU Past 30 Days has incidents in the past 30 days by BU. The number needs to be the amount of incidents that are 30 days or older and that belong to that BU. The second time is the total down time. Total down time is the amount of minutes the server was down. The percentage Up time should be the number of minutes the server was down divided by the time the servers was up (minutes in 30 days). I have pre-populated the excel sheet with data to make the formulas work. To complete this task, you will need to create forumulas for the B, C, and D columns for the second worksheet and these columns should adjust themselves accordingly based on the data that is entered on the 1st sheet.  Any ideas or a better approach to this type of collection?
Server-Outage-Tracking-Draft.zip
LVL 1
securitythreatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ericathomeCommented:
See if the attached helps. You may want to adjust hours for uptime percentages depending on how many hours a day servers are used.

Let me know if you need any changes.

Thanks, Eric
Server-Outage-Tracking-Draft.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello securitythreat.

Please note that ericathome's file does not calculate what you want. I have added a few more rows of data to create a range of dates that goes back more than 30 days. Ericathome's version does not take the last 30 days into account at all, but simply takes the whole data from the outage sheet.

I have created three dynamic range names that will grow with the data

BusinessUnit      =Outages!$F$31:INDEX(Outages!$F:$F,MATCH(99^99,Outages!$A:$A,1))
DownTime      =Outages!$D$31:INDEX(Outages!$D:$D,MATCH(99^99,Outages!$A:$A,1))
ODate      =Outages!$A$31:INDEX(Outages!$A:$A,MATCH(99^99,Outages!$A:$A,1))

To calculate the Incidents per BU in the last 30 days
=COUNTIFS(BusinessUnit,A2,ODate,">="&TODAY()-30)

Total down time in the last 30 days
=SUMIFS(DownTime,BusinessUnit,A2,ODate,">="&TODAY()-30)
formatted with custom format [hh]:mm:ss

This custom format will give you correct hour values even when the hours are more than 24. In ericathome's file, the date portion would be cut off, and you would see 25:34:00 as 1:34:00, which can be quite misleading.

Percentage uptime for the last 30 days
=1-C2/(60*24*30)

Please see attached file.

cheers, teylyn
Q-25600843-Outage-calculations.xlsx
0
ericathomeCommented:
good catch - I did see the 30 day requirement.
0
securitythreatAuthor Commented:
This worked.. I will probably have more help request.. but thanks.. this is a great start.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.