Hi,<o></o>
<o></o>
I have quite a challenging procedure that needs the excel MVPs expertise.<o></o>
I have been asked to calculate if we are meeting the required SLA in processing our departments complaints and check if we are adhering or not coz currently everyone is just putting in “On Time” in the file<o></o>
<o></o>
Here it goes.<o></o>
We receive complaints from our customers on a daily basis 247/7.<o></o>
And we only work Mon-Fri from 8am-6pm<o></o>
We have a cut-off of 4pm on weekdays when a complaint has been submitted.<o></o>
Any complaint submitted after 4pm will be accommodated on the next working day<o></o>
We also need to consider public holidays. If a public holiday falls on a Friday or Monday, the complaint will be attended to on the first business day after holiday.<o></o>
<o></o>
We have this SLA to our customers<o></o>
Premium – within 4 hrs<o></o>
High Value – within 6 hrs<o></o>
Low value – till end of next business day<o></o>
<o></o>
The table below shows the different types of customers and sample public holidays<o></o>
In the SLA column I would like to show if the SLA was “On Time” or “Late”<o></o>
Am not sure of what function to use for the date and it’s the first time I tackled this kind of situation.
<o></o>
Thank you in advance
<TBODY>
</TBODY></SPAN>
<o></o>
I have quite a challenging procedure that needs the excel MVPs expertise.<o></o>
I have been asked to calculate if we are meeting the required SLA in processing our departments complaints and check if we are adhering or not coz currently everyone is just putting in “On Time” in the file<o></o>
<o></o>
Here it goes.<o></o>
We receive complaints from our customers on a daily basis 247/7.<o></o>
And we only work Mon-Fri from 8am-6pm<o></o>
We have a cut-off of 4pm on weekdays when a complaint has been submitted.<o></o>
Any complaint submitted after 4pm will be accommodated on the next working day<o></o>
We also need to consider public holidays. If a public holiday falls on a Friday or Monday, the complaint will be attended to on the first business day after holiday.<o></o>
<o></o>
We have this SLA to our customers<o></o>
Premium – within 4 hrs<o></o>
High Value – within 6 hrs<o></o>
Low value – till end of next business day<o></o>
<o></o>
The table below shows the different types of customers and sample public holidays<o></o>
In the SLA column I would like to show if the SLA was “On Time” or “Late”<o></o>
Am not sure of what function to use for the date and it’s the first time I tackled this kind of situation.
<o></o>
Thank you in advance
PH1</SPAN> | 01/01/2012</SPAN> | |||
PH2</SPAN> | 10/08/2012</SPAN> | |||
PH3</SPAN> | 20/08/2012</SPAN> | |||
PH4</SPAN> | 25/12/2012</SPAN> | |||
CID</SPAN> | Customer Type</SPAN> | Date Submitted</SPAN> | Date Closed</SPAN> | SLA</SPAN> |
8353</SPAN> | Premium</SPAN> | 09/08/2012 17:25:26</SPAN> | 13/08/2012 12:35:17</SPAN> | Late |
7046</SPAN> | High</SPAN> | 13/08/2012 09:00:00</SPAN> | 13/08/2012 14:00:00</SPAN> | On Time</SPAN> |
7304</SPAN> | High</SPAN> | 13/08/2012 20:18:00</SPAN> | 14/08/2012 18:25:00</SPAN> | Late |
7209</SPAN> | High</SPAN> | 13/08/2012 11:06:00</SPAN> | 15/08/2012 15:03:00</SPAN> | On Time</SPAN> |
5758</SPAN> | Low</SPAN> | 13/08/2012 15:44:00</SPAN> | 13/08/2012 17:49:00</SPAN> | On Time</SPAN> |
5019</SPAN> | Low</SPAN> | 13/08/2012 18:57:00</SPAN> | 14/08/2012 09:57:00</SPAN> | On Time</SPAN> |
8976</SPAN> | Premium</SPAN> | 19/08/2012 16:24:56</SPAN> | 21/08/2012 11:46:06</SPAN> | On Time</SPAN> |
8079</SPAN> | Premium</SPAN> | 19/08/2012 15:59:12</SPAN> | 21/08/2012 11:00:00</SPAN> | Late |
8333</SPAN> | Premium</SPAN> | 20/08/2012 20:54:00</SPAN> | 21/08/2012 12:34:00</SPAN> | Late |
<TBODY>
</TBODY>