Adding Date and Time minus not working hours

[Firasath]

Hi,

I have a data of complaints where I need to present it to the Management in such a way that the SLA period of 8 hours does not pass. Our office working hours are 7 AM till 7 PM. The complaint received should be escalated to concern section within 8 Hours of SLA time. I have the list of dates with received time. The complaint which could not be escalated today would be escalated next day. In this case is should deduct 12 Hours (7 PM to 7 AM, Non-working hours) from the time. Please advise how can I insert escalation date so as that it would deduct non working hours from it.

Regards,

Firasath

 

[mohanyathish]

Hi...

I suggest you post an excel with sample data...

 

[Firasath]

<table border="0" cellpadding="0" cellspacing="0" width="348"><col style="mso-width-source:userset;mso-width-alt:4973; width:102pt" span="2" width="136"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;width:102pt; font-size:11.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="136">Received Date</td> <td class="xl68" style="width:102pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none;background:#4F81BD;mso-pattern:#4F81BD none" width="136">Escalated Date</td> <td class="xl68" style="width:57pt;font-size:11.0pt;color:white; font-weight:700;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none;background:#4F81BD;mso-pattern:#4F81BD none" width="76">Duration</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">30/08/2011 04:21 PM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 07:18 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">14:57</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">30/08/2011 09:43 AM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 12:11 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">14:28</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">30/08/2011 09:43 PM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 10:23 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">12:40</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">30/08/2011 09:43 PM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 10:00 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">12:17</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">31/08/2011 04:21 PM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">01/09/2011 07:23 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">15:02</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">31/08/2011 09:43 PM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">01/09/2011 07:23 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">9:40</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">31/08/2011 09:43 AM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 12:24 PM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">2:41</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">31/08/2011 09:43 AM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 11:11 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">1:28</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;font-size:11.0pt; color:black;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #4F81BD; border-right:none;border-bottom:none;border-left:.5pt solid #4F81BD" align="right" height="20">31/08/2011 09:43 AM</td> <td class="xl66" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:none; border-bottom:none;border-left:none" align="right">31/08/2011 09:56 AM</td> <td class="xl67" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #4F81BD;border-right:.5pt solid #4F81BD; border-bottom:none;border-left:none" align="right">0:13</td> </tr> </tbody></table>
If you observe here, after subtracting it is not considering the non working hours from 7 PM to 7 AM. I want the formula to calculate the duration according to the working hours starting from 7 AM to 7 PM. Please advise.

 

[barry houdini]

If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

=(NETWORKDAYS(A2,B2)-1)*("19:00"-"7:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"19:00","7:00"),"19:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"19:00","7:00")

format C2 as [h]:mm
<!-- / message -->

 

[Firasath]

Sorry for reply you back so lately. But I tried the above formual, but it is not giving wrong result in the form of (#####). Please advise.

 

[barry houdini]

#### either indicates a negative time (which excel can't display in normal circumstances) or that the column is too narrow to show the result. What result do you see if you format the result cell as number (temporarily)?

Did you use the exact formula I suggested or have you amended it at all?