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

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.

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.
Kind Regards
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.

Glenn RayExcel VBA DeveloperCommented:
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)"

macentrapAuthor 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
Glenn RayExcel VBA DeveloperCommented:
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.


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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Glenn RayExcel VBA DeveloperCommented:
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)
macentrapAuthor Commented:
Thanks Glenn,
is it possible with VBA please

Kind Regards
macentrapAuthor Commented:
also, Please could you help how could I group incident data in 30 min interval.
Just on the incident tab
macentrapAuthor 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 DeveloperCommented:
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.

macentrapAuthor Commented:
Thank You Glenn,
sure will submit new question for users
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.