SLA and Response time metric help...

[calvinyhob]

Ok, ive gone about this a few different ways and im stuck now and hoping some of you experts can help me out! Sorry for the long winded post but hoping i can give you as much data as required to help me figure out what i am doing wrong.

Back Story...
So i run a weekly report from our helpdesk system so that i can report out on metrics for our new boss(attached is a sample export from the report). Right now ive had a few people around the office weigh in on this trying to get accurate data and where we are at now is attached. To me the data is not 100% accurate as some of the formulas are not working as expected so new columns have been added trying to resolve but these are not accurate either. Below are columns we have added and their intended purpose and any short comings i see.

Desired end Result
-Simplify this reporting so it can be completed much easier. I would prefer to remove columns R, S, T and only use the rest if someone could tell me why my formulas in U and V do not calculate properly. Example would be if i just use those 2 columns i will get response time 10x higher then open time so i know something is wrong somewhere.
-Get accurate data to present to the teams
-Column U- If possible id like this to automatically say "No Response" if Column P is empty

Metrics
Priority Resolution Time (hrs) Core Hours (Mon-Fri)
Critical 4 08:00-17:00
High 8 08:00-17:00
Normal 40 08:00-17:00
Low 80 08:00-17:00



Priority Response Time (hrs) Core Hours (Mon-Fri)
Critical 0.5 08:00-17:00
High 2 08:00-17:00
Normal 8 08:00-17:00
Low 16 08:00-17:00


Column B- networkday between S and Q (think im good here)
Column C- Verify if our SLA times are met based on column E "priority" and column V "Hours Open" (think im good here)
Column D- Verify if our response times are met based on column E "priority" and column U "Response Time" in hours (think im good here)
Column Q- Manually added Date and time of when the report is ran
Column R- This is useless, honestly need to be removed i just havent done it today.
Column S- This looks at the open date and if the time is after 5pm it add 1 day and starts at 8:00am
Issue with S- Issue i have is i need to account for 5pm to 8am and have it start the next day if its 5pm-midnight or from midnight-8am move start time to 8am
Column T- Looks at the start time and if the time is after 5pm it add 1 day and starts at 8:00am
Issue with S- Issue i have is i need to account for 5pm to 8am and have it start the next day if its 5pm-midnight or from midnight-8am move start time to 8am
Column U- this was mean to automatically remove non-working hours and weekends to give me the hours from from open time to the start time. Unfortunantly this never seemed to work right which lead the team to columns r, s and t.
Column V- same as above but was mean to give me the hours open between O and Q removing weekend, and after hours 5pm to 8am. Again this never worked the way it should and lead to other columns.

I think if someone could help me figure out what im doing wrong in columns U and V i can remove R, S and T.