Date/Time/Public Holidays

jayjavina

New Member
Joined
Feb 3, 2009
Messages
34
Hi,<o:p></o:p>
<o:p></o:p>
I have quite a challenging procedure that needs the excel MVPs expertise.<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Here it goes.<o:p></o:p>
We receive complaints from our customers on a daily basis 247/7.<o:p></o:p>
And we only work Mon-Fri from 8am-6pm<o:p></o:p>
We have a cut-off of 4pm on weekdays when a complaint has been submitted.<o:p></o:p>
Any complaint submitted after 4pm will be accommodated on the next working day<o:p></o:p>
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:p></o:p>
<o:p></o:p>
We have this SLA to our customers<o:p></o:p>
Premium – within 4 hrs<o:p></o:p>
High Value – within 6 hrs<o:p></o:p>
Low value – till end of next business day<o:p></o:p>
<o:p></o:p>
The table below shows the different types of customers and sample public holidays<o:p></o:p>
In the SLA column I would like to show if the SLA was “On Time” or “Late”<o:p></o:p>
Am not sure of what function to use for the date and it’s the first time I tackled this kind of situation.

<o:p></o:p>

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>
</SPAN>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi,

Just had a quick read through your post and want to check some logic. :confused:

If a complaint is received after 16:00 then the complaint will be serviced next working day, whatever the level of service the customer is entitled to. So, if a Premium customer complains after 16:00, their call must be serviced by 12:00 on the following working day?

What if a Premium customer complains at 15:00? Must the call be completed same day (i.e. within 3 hours) or is 09:00 on the following working day acceptable?

Are the SLA values in your example an accurate representation of what you want as the output? e.g. for CID 7209 the date range covers three working days, with a rating of High, but is shown as "On Time". Surely this should be "Late"?
 
Upvote 0
Hi! Thanks for taking the time to read my post.

Whatever complaint received after 1600, it will be serviced the next day from 0800 till end of day meaning till 2400, but its only till end of business day.

For a Premium customer, if it was received at 1500, by right, the complaint must be resolved within 4hrs. But due to unavoidable situation e.g. slow system, it will be "manually" adjusted.

The SLA values are just examples since these are manually keyed in.

Appreciate the help!:)
 
Upvote 0
But due to unavoidable situation e.g. slow system, it will be "manually" adjusted.

Then the SLA doesn't really make sense because it has inbuilt impossible targets, but that's a different matter!

So how about something like this to (mostly) calculate appropriate targets:

Sheet1

*
A
B
C
D
E
F
G
H
1
CID
Customer Type
Date Submitted
Date Closed
SLA
Target Date
Target Time
Public Holidays
2
8353
Premium
Wed 08/08/2012 17:25
Mon 13/08/2012 12:35
Late
Thu 09/08/2012
12:00:00
Mon 02/01/2012 00:00
3
7046
High
Mon 13/08/2012 09:00
Mon 13/08/2012 14:00
On Time
Mon 13/08/2012
15:00:00
Fri 10/08/2012 00:00
4
7304
High
Mon 13/08/2012 20:18
Tue 14/08/2012 18:25
Late
Tue 14/08/2012
14:00:00
Mon 20/08/2012 00:00
5
7209
High
Mon 13/08/2012 11:06
Wed 15/08/2012 15:03
Late
Mon 13/08/2012
17:06:00
Tue 25/12/2012 00:00
6
5758
Low
Mon 13/08/2012 15:44
Mon 13/08/2012 17:49
On Time
Tue 14/08/2012
18:00:00
*
7
5019
Low
Mon 13/08/2012 18:57
Tue 14/08/2012 09:57
On Time
Tue 14/08/2012
18:00:00
*
8
8976
Premium
Sun 19/08/2012 16:24
Tue 21/08/2012 11:46
On Time
Tue 21/08/2012
12:00:00
*
9
8079
Premium
Sun 19/08/2012 15:59
Tue 21/08/2012 11:00
On Time
Tue 21/08/2012
19:59:12
*
10
8333
Premium
Mon 20/08/2012 20:54
Tue 21/08/2012 12:34
Late
Tue 21/08/2012
12:00:00
*

<tbody>
</tbody>

Spreadsheet Formulas

Cell
Formula
E2
=IF(D2>F2+G2,"Late","On Time")
F2
=IF(OR(C2-INT(C2)>16/24,WEEKDAY(C2,2)>5,NETWORKDAYS(C2,C2,$H$2:$H$5)=0,B2="Low"),IF(NETWORKDAYS(C2+1,C2+1,$H$2:$H$5)=1,INT(C2)+1,IF(NETWORKDAYS(C2+2,C2+2,$H$2:$H$5)=1,INT(C2)+2,IF(NETWORKDAYS(C2+3,C2+3,$H$2:$H$5)=1,INT(C2)+3,IF(NETWORKDAYS(C2+4,C2+4,$H$2:$H$5)=1,INT(C2)+4,IF(NETWORKDAYS(C2+5,C2+5,$H$2:$H$5)=1,INT(C2)+5))))),INT(C2))
G2
=IF(B2="Premium",IF(C2-INT(C2)>16/24,12/24,C2-INT(C2)+4/24),IF(B2="High",IF(C2-INT(C2)>16/24,14/24,C2-INT(C2)+6/24),18/24))

<tbody>
</tbody>

<tbody>
</tbody>

(... and copy the formulae down columns E to G)

Excel tables to the web >> Excel Jeanie Html
 
Upvote 0
Ooops - spotted a little slip in my logic for the target times - try this instead!

Sheet1

*
A
B
C
D
E
F
G
H
1
CID
Customer Type
Date Submitted
Date Closed
SLA
Target Date
Target Time
Public Holidays
2
8353
Premium
Wed 08/08/2012 17:25
Mon 13/08/2012 12:35
Late
Thu 09/08/2012
12:00:00
Mon 02/01/2012 00:00
3
7046
High
Mon 13/08/2012 09:00
Mon 13/08/2012 14:00
On Time
Mon 13/08/2012
15:00:00
Fri 10/08/2012 00:00
4
7304
High
Mon 13/08/2012 20:18
Tue 14/08/2012 18:25
Late
Tue 14/08/2012
14:00:00
Mon 20/08/2012 00:00
5
7209
High
Mon 13/08/2012 11:06
Wed 15/08/2012 15:03
Late
Mon 13/08/2012
17:06:00
Tue 25/12/2012 00:00
6
5758
Low
Mon 13/08/2012 15:44
Mon 13/08/2012 17:49
On Time
Tue 14/08/2012
18:00:00
*
7
5019
Low
Mon 13/08/2012 18:57
Tue 14/08/2012 09:57
On Time
Tue 14/08/2012
18:00:00
*
8
8976
Premium
Sun 19/08/2012 16:24
Tue 21/08/2012 11:46
On Time
Tue 21/08/2012
12:00:00
*
9
8079
Premium
Sun 19/08/2012 15:59
Tue 21/08/2012 11:00
On Time
Tue 21/08/2012
12:00:00
*
10
8333
Premium
Mon 20/08/2012 20:54
Tue 21/08/2012 12:34
Late
Tue 21/08/2012
12:00:00
*

<tbody>
</tbody>

Spreadsheet Formulas

Cell
Formula
E2
=IF(D2>F2+G2,"Late","On Time")
F2
=IF(OR(C2-INT(C2)>16/24,WEEKDAY(C2,2)>5,NETWORKDAYS(C2,C2,$H$2:$H$5)=0,B2="Low"),IF(NETWORKDAYS(C2+1,C2+1,$H$2:$H$5)=1,INT(C2)+1,IF(NETWORKDAYS(C2+2,C2+2,$H$2:$H$5)=1,INT(C2)+2,IF(NETWORKDAYS(C2+3,C2+3,$H$2:$H$5)=1,INT(C2)+3,IF(NETWORKDAYS(C2+4,C2+4,$H$2:$H$5)=1,INT(C2)+4,IF(NETWORKDAYS(C2+5,C2+5,$H$2:$H$5)=1,INT(C2)+5))))),INT(C2))
G2
=IF(B2="Premium",IF(OR(C2-INT(C2)>16/24,NETWORKDAYS(C2,C2,$H$2:$H$5)=0),12/24,C2-INT(C2)+4/24),IF(B2="High",IF(OR(C2-INT(C2)>16/24,NETWORKDAYS(C2,C2,$H$2:$H$5)=0),14/24,C2-INT(C2)+6/24),18/24))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html
 
Upvote 0
It works just fine! Many thanks! :)

Is there a way for the target date to show the time too? Its not much but good to have in one column rather than two.

this is what i did, a very minor addition in the formula if the close date is still blank.
=IF(D2="","Pending",IF(D2>=F2+G2,"Late","On Time"))

All good!
thank you!
 
Upvote 0
Hi,

Yes, try this:

Sheet1

*
A
B
C
D
E
F
G
H
1
CID
Customer Type
Date Submitted
Date Closed
SLA
Target Date
*
Public Holidays
2
8353
Premium
Wed 08/08/2012 17:25
Mon 13/08/2012 12:35
Late
Thu 09/08/2012 12:00
*
Mon 02/01/2012 00:00
3
7046
High
Mon 13/08/2012 09:00
Mon 13/08/2012 14:00
On Time
Mon 13/08/2012 15:00
*
Fri 10/08/2012 00:00
4
7304
High
Mon 13/08/2012 20:18
Tue 14/08/2012 18:25
Late
Tue 14/08/2012 14:00
*
Mon 20/08/2012 00:00
5
7209
High
Mon 13/08/2012 11:06
Wed 15/08/2012 15:03
Late
Mon 13/08/2012 17:06
*
Tue 25/12/2012 00:00
6
5758
Low
Mon 13/08/2012 15:44
*
Pending
Tue 14/08/2012 18:00
*
*
7
5019
Low
Mon 13/08/2012 18:57
Tue 14/08/2012 09:57
On Time
Tue 14/08/2012 18:00
*
*
8
8976
Premium
Sun 19/08/2012 16:24
Tue 21/08/2012 11:46
On Time
Tue 21/08/2012 12:00
*
*
9
8079
Premium
Sun 19/08/2012 15:59
Tue 21/08/2012 11:00
On Time
Tue 21/08/2012 12:00
*
*
10
8333
Premium
Mon 20/08/2012 20:54
Tue 21/08/2012 12:34
Late
Tue 21/08/2012 12:00
*
*

<tbody>
</tbody>

Spreadsheet Formulas

Cell
Formula
E2
=IF(D2="","Pending",IF(D2>=F2,"Late","On Time"))
F2
=IF(OR(C2-INT(C2)>16/24,WEEKDAY(C2,2)>5,NETWORKDAYS(C2,C2,$H$2:$H$5)=0,B2="Low"),IF(NETWORKDAYS(C2+1,C2+1,$H$2:$H$5)=1,INT(C2)+1,IF(NETWORKDAYS(C2+2,C2+2,$H$2:$H$5)=1,INT(C2)+2,IF(NETWORKDAYS(C2+3,C2+3,$H$2:$H$5)=1,INT(C2)+3,IF(NETWORKDAYS(C2+4,C2+4,$H$2:$H$5)=1,INT(C2)+4,IF(NETWORKDAYS(C2+5,C2+5,$H$2:$H$5)=1,INT(C2)+5))))),INT(C2))+IF(B2="Premium",IF(OR(C2-INT(C2)>16/24,NETWORKDAYS(C2,C2,$H$2:$H$5)=0),12/24,C2-INT(C2)+4/24),IF(B2="High",IF(OR(C2-INT(C2)>16/24,NETWORKDAYS(C2,C2,$H$2:$H$5)=0),14/24,C2-INT(C2)+6/24),18/24))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie Html
 
Upvote 0
Thanks much!

Am still trying to fully understand the whole logic of formula...

but really appreciate the help.
Now am trying to calculate how many hours it took them to finish the job but i cant convert it to hh:mm:ss.

I tried changing the number format but it was a bad idea. It shows me numbers which is correct when computed as general. I know there is a simple formula but I just cant figure it out.

this is what i have (I moved the holiday column to J2)
same rule applies for the working hours(830-6) and weekend sat sun and ph

Cell H2
=(NETWORKDAYS(C2,D2,J$2:J$5)-1)*(17-8)+IF(NETWORKDAYS(D2,D2,J$2:J$5),MEDIAN(MOD(D2,1)*24,8,17),17)-MEDIAN(MOD(C2,1)*24*NETWORKDAYS(C2,C2,J$2:J$5),8,17)

Answer shows 13.58 which is ok but how to translate it to time hh:mm:ss. Directly translating it to hh:mm:ss will be inaccurate.

Thanks a lot!
You've been a great help.
 
Upvote 0
To get a time value result change to

=(NETWORKDAYS(C2,D2,J$2:J$5)-1)*(17-8)/24+IF(NETWORKDAYS(D2,D2,J$2:J$5),MEDIAN(MOD(D2,1),8/24,17/24),17/24)-MEDIAN(MOD(C2,1)*NETWORKDAYS(C2,C2,J$2:J$5),8/24,17/24)

....and custom format result cell as [h]:mm:ss
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top