VBA - to calculate server uptime along with incident data in 30 minutes interval

macentrap
macentrap used Ask the Experts™
on
Hi Experts,

in attached excel file, the is
"server uptime" tab- this provides the server uptime in 30 min interval where data is not available it gets reflected in "Summary" tab as "pass" or "fail".

 "Incidents-logged":  Also included is the number of incidents logged in during the day with date and time.

I would like to have extra information on the Summary tab:

if there is incident logged during the day,  the count of incident should reflect the 30 minute time slot.


Example:
if there 4 incidents on 1 july between time slot 8am to 8.30am then
pass data should reflect

Pass (4) in the the summary tab .


Please could you help me.
Server-uptime.xlsx
Kind Regards
macentrap.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Clarifications needed:
1) Is it possible that there are incidents while the status is "Pass"?  
2) What about "Fail" conditions; do you also want to see the count of incidents?

For example, based on your actual data
1) Summary: July 1, 8:00 (cell C18) would now display "Pass (1)"
July 1, 8:30 (cell C19) would now display "Pass (2)"
July 1, 9:00 (cell C20) would remain the same "Pass"
July 1, 9:00 (cell C21) would now display "Pass (2)"
and so on
2) July 7, 8:30 (cell I19) would now show "Fail (2)"

-Glenn

Author

Commented:
Hi Glenn
Yes its possible to have incidents when server uptime has "pass"
So does the "fail".

The monitoring tool on server is giving false positive n false negatives, hence trying to present the data along with incidents which are logged when end user reports n outage.

Thank you
Excel VBA Developer
Top Expert 2014
Commented:
I have a non-VBA solution for you if you don't mind the long formula.

Insert in cell C2 and copy down and across:
=IF(C$1<=TODAY()-1,IF(ISERROR(VLOOKUP(TEXT(C$1,"d/m/yyy")&TEXT($B2,"h:mm"),'server uptime'!$K:$L,2,FALSE)),"Fail","Pass"),"") & IF(COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00")))>0," ("&COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00")))&")","")
(The bolded section is added to your original formula)

To simplify the formula I added a named range, "Incidents" which covers 'Incidents-logged'!$A$4:$A$590.  Example workbook attached.

I also modified the conditional formatting rules to account for the possible changes in the values.

-Glenn
EE-Server-uptime.xlsx
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
If you could do without the paranthesis around the incident count (ex., "Pass 3" instead of "Pass (3)") the formula could be a little shorter:
=IF(C$1<=TODAY()-1,IF(ISERROR(VLOOKUP(TEXT(C$1,"d/m/yyy")&TEXT($B2,"h:mm"),'server uptime'!$K:$L,2,FALSE)),"Fail","Pass"),"") & " " & IFERROR(1/(1/(COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00"))))),"")
(bold part added/changed)

Author

Commented:
Thanks Glenn,
is it possible with VBA please

Kind Regards

Author

Commented:
also, Please could you help how could I group incident data in 30 min interval.
Just on the incident tab

Author

Commented:
Hi Glenn,
they other bit which I forgot was the "server uptime" has the users information connected at each timeslot would it possbile to have that inluded in Summary too please .

Sorry i just realized
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
Frankly, the formula works so well, I would be inclined NOT to change it.  It is instanteous and will work on larger data ranges so long as the named range is updated ("Incidents").  A VBA approach would require some event to refresh the data (either a macro run, a button, a Worksheet_Change event, etc.) and, if not re-creating this function, would overwrite the data in all the Summary cells each time.

That said, you have now added two additional requests beyond the scope of the original question.  The first question - adding 30-min interval captures on the Incident tab - is already answered by the logic used to solve the first question (i.e., the compount COUNTIF function).  However, you need to specify exactly how you want this result to be shown.  Note that for the month of July there are 1,488 results on the Summary sheet (half-hour increments over 31 days).

The second question would require another example file that shows the user information you speak of.  IMO, that should be a separate question altogether.

Regards,
-Glenn

Author

Commented:
Thank You Glenn,
sure will submit new question for users

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial