Sounds_Logical
New Member
- Joined
- Apr 4, 2011
- Messages
- 2
Hi All,
I need to calculate the Elapsed Time in [H]:MM between two dates taking into account a variety of working hours which are allowed on Weekdays, Weekends and Public Holidays.
Take for example the following date range:
The table used to determine working hours is as follows:
<table border="0" cellpadding="0" cellspacing="0" width="388"><col style="width: 27pt;" width="36"> <col style="width: 110pt;" width="147"> <col style="width: 58pt;" width="77"> <col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl30" style="height: 15.75pt; width: 27pt;" height="21" width="36">Type</td> <td class="xl31" style="border-left: medium none; width: 110pt;" width="147">Calendar Description</td> <td class="xl30" style="border-left: medium none; width: 58pt;" width="77">Days</td> <td class="xl32" style="border-left: medium none; width: 48pt;" width="64">Start</td> <td class="xl33" style="border-left: medium none; width: 48pt;" width="64">End</td> </tr> <tr style="height: 17.25pt;" height="23"> <td class="xl26" style="height: 17.25pt;" height="23">11</td> <td class="xl27" style="border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-left: medium none;">Sunday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl29" style="border-top: medium none;"> </td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt; border-top: medium none;" height="18">12</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Monday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">7:00</td> <td class="xl29" style="border-top: medium none;" align="right">20:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">13</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Tuesday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">6:00</td> <td class="xl29" style="border-top: medium none;" align="right">20:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">14</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Wednesday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">6:00</td> <td class="xl29" style="border-top: medium none;" align="right">20:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">15</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Thursday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">6:00</td> <td class="xl29" style="border-top: medium none;" align="right">20:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">16</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Friday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">6:00</td> <td class="xl29" style="border-top: medium none;" align="right">19:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">17</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Saturday</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">9:00</td> <td class="xl29" style="border-top: medium none;" align="right">13:00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">18</td> <td class="xl27" style="border-top: medium none; border-left: medium none; width: 110pt;" width="147">Extended Hrs</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Public Hols</td> <td class="xl29" style="border-top: medium none; border-left: medium none;" align="right">9:00</td> <td class="xl29" style="border-top: medium none;" align="right">13:00</td> </tr> </tbody></table>
Other tables are used for storing Public Holiday Dates such as the one below:
<table border="0" cellpadding="0" cellspacing="0" width="784"><col style="width: 137pt;" width="183"> <col style="width: 114pt;" width="152"> <col style="width: 76pt;" width="101"> <col style="width: 65pt;" span="2" width="86"> <col style="width: 66pt;" span="2" width="88"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt; width: 137pt;" height="18" width="183">Public Hols used in Rpt Mth</td> <td class="xl27" style="border-left: medium none; width: 114pt;" width="152">UK Public Holidays</td> <td class="xl28" style="width: 76pt;" width="101">2010</td> <td class="xl28" style="width: 65pt;" width="86">2011</td> <td class="xl28" style="width: 65pt;" width="86">2012</td> <td class="xl28" style="width: 66pt;" width="88">2013</td> <td class="xl28" style="width: 66pt;" width="88">2014</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt; border-top: medium none;" height="17">Tue 01/03/11</td> <td class="xl30" style="border-left: medium none;">New Years Day (or lieu)</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Fri 01/01/10</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Tue 01/03/11</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Mon 02/01/12</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Tue 01/01/13</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Wed 01/01/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Fri 22/04/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Easter Friday</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 02/04/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 22/04/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 06/04/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 29/03/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 18/04/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 25/04/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Easter Monday</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 05/04/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 25/04/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 09/04/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 01/04/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 21/04/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 02/05/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">May Day BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 03/05/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 02/05/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 07/05/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 06/05/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 05/05/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 30/05/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Spring BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 31/05/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 30/05/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 04/06/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/05/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/05/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 29/08/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Summer BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 30/08/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 29/08/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/08/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/08/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 25/08/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 26/12/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Christmas Day (or lieu)</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/12/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/12/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 25/12/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Wed 25/12/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Thu 25/12/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Tue 27/12/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Boxing Day (or lieu)</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 28/12/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 27/12/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Wed 26/12/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Thu 26/12/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 26/12/14</td> </tr> </tbody></table>
I've manage to cobble together a number of formulas to do this calculation however the formulas are quite long (approaching the 1024 char limit) and the calculation time becomes are real problem when several thousand records need to be processed.
The layout of the Data and formulas is as per the example below with the Purple text showing Data and the Blue text indicating formula's:
<table border="0" cellpadding="0" cellspacing="0" width="1234"><col style="width: 48pt;" width="64"> <col style="width: 88pt;" width="117"> <col style="width: 86pt;" width="115"> <col style="width: 42pt;" width="56"> <col style="width: 38pt;" width="50"> <col style="width: 48pt;" span="13" width="64"> <tbody><tr style="height: 33.75pt;" height="45"> <td class="xl25" style="height: 33.75pt; width: 48pt;" height="45" width="64">TICKETID</td> <td class="xl25" style="border-left: medium none; width: 88pt;" width="117">Start</td> <td class="xl25" style="border-left: medium none; width: 86pt;" width="115">Finish</td> <td class="xl25" style="border-left: medium none; width: 42pt;" width="56">Priority</td> <td class="xl25" style="border-left: medium none; width: 38pt;" width="50">Status</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Service Hours Type</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Mon</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Tue</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Wed</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Thu</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Fri</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Start Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Middle Hours</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">End Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Bank Hols Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Same Day Elapsed</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">SLAHOLD Time</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Elapsed Time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; border-top: medium none; width: 48pt;" height="17" width="64">IN0002</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 88pt;" align="right" width="117">Thu 21/04/2011 12:00</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 86pt;" align="right" width="115">Tue 31/05/2011 17:00</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 42pt;" align="right" width="56"> 2</td> <td class="xl30" style="border-top: medium none; border-left: medium none; width: 38pt;" width="50">CLOSED</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">3</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">8:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">338:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">11:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">16:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">0:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">0:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">373:00:00</td> </tr> </tbody></table>
The Formula to Count Public Holidays occurring on a Monday is as follows:
=IF(C2<B2,"Open or Error",IF(INT(C2)-INT(B2)<=1,0,IF(WEEKDAY('Service Hours'!$L$4)=2,IF(AND('Service Hours'!$L$4>=INT(B2)+1,'Service Hours'!$L$4<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$5)=2,IF(AND('Service Hours'!$L$5>=INT(B2)+1,'Service Hours'!$L$5<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$6)=2,IF(AND('Service Hours'!$L$6>=INT(B2)+1,'Service Hours'!$L$6<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$7)=2,IF(AND('Service Hours'!$L$7>=INT(B2)+1,'Service Hours'!$L$7<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$8)=2,IF(AND('Service Hours'!$L$8>=INT(B2)+1,'Service Hours'!$L$8<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$9)=2,IF(AND('Service Hours'!$L$9>=INT(B2)+1,'Service Hours'!$L$9<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$10)=2,IF(AND('Service Hours'!$L$10>=INT(B2)+1,'Service Hours'!$L$10<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$11)=2,IF(AND('Service Hours'!$L$11>=INT(B2)+1,'Service Hours'!$L$11<=INT(C2)-1),1,0))))
The approach to the Elapsed Time calculation is as follows:
a - If the Start and End time is on the same day a single calculation is performed
b - If the Start and End times are on different days the calculation is accomplished in four parts as follows:
1 - Work out any hours on Start day
2 - Work out any hours on End day
3 - Work out any hours in the middle i.e not Start or End plus deduct Public Holiday count
4 - Work out any hours occurring on a Public Holiday
c - The Working Hours to use for each ticket is decided based on Priority with an appended number to represent days Sun to Sat & Public Hols
I have example data and formulas in a workbook but wasn't sure how to attach it to this post.
I'd appreciate any suggestions as to how this could be improved either by by changing the logic / approach or suggested Formula changes or even using User Defined Functions.
Currently using Excel 2003.
Thanks in advance
Ian
I need to calculate the Elapsed Time in [H]:MM between two dates taking into account a variety of working hours which are allowed on Weekdays, Weekends and Public Holidays.
Take for example the following date range:
Start Date - 21/04/2011 12:00:00
End Date - 31/05/2011 17:00:00
Within this date range there are 4 UK Public Holidays 6 Weekends and a number of standard Working Days.End Date - 31/05/2011 17:00:00
The table used to determine working hours is as follows:
Other tables are used for storing Public Holiday Dates such as the one below:
<table border="0" cellpadding="0" cellspacing="0" width="784"><col style="width: 137pt;" width="183"> <col style="width: 114pt;" width="152"> <col style="width: 76pt;" width="101"> <col style="width: 65pt;" span="2" width="86"> <col style="width: 66pt;" span="2" width="88"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl26" style="height: 13.5pt; width: 137pt;" height="18" width="183">Public Hols used in Rpt Mth</td> <td class="xl27" style="border-left: medium none; width: 114pt;" width="152">UK Public Holidays</td> <td class="xl28" style="width: 76pt;" width="101">2010</td> <td class="xl28" style="width: 65pt;" width="86">2011</td> <td class="xl28" style="width: 65pt;" width="86">2012</td> <td class="xl28" style="width: 66pt;" width="88">2013</td> <td class="xl28" style="width: 66pt;" width="88">2014</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt; border-top: medium none;" height="17">Tue 01/03/11</td> <td class="xl30" style="border-left: medium none;">New Years Day (or lieu)</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Fri 01/01/10</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Tue 01/03/11</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Mon 02/01/12</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Tue 01/01/13</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Wed 01/01/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Fri 22/04/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Easter Friday</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 02/04/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 22/04/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 06/04/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 29/03/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 18/04/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 25/04/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Easter Monday</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 05/04/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 25/04/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 09/04/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 01/04/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 21/04/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 02/05/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">May Day BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 03/05/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 02/05/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 07/05/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 06/05/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 05/05/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 30/05/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Spring BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 31/05/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 30/05/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 04/06/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/05/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/05/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 29/08/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Summer BH</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 30/08/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 29/08/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/08/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/08/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 25/08/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Mon 26/12/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Christmas Day (or lieu)</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 27/12/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Mon 26/12/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 25/12/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Wed 25/12/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Thu 25/12/14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt; border-top: medium none;" height="17">Tue 27/12/11</td> <td class="xl33" style="border-top: medium none; border-left: medium none;">Boxing Day (or lieu)</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 28/12/10</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Tue 27/12/11</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Wed 26/12/12</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Thu 26/12/13</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">Fri 26/12/14</td> </tr> </tbody></table>
I've manage to cobble together a number of formulas to do this calculation however the formulas are quite long (approaching the 1024 char limit) and the calculation time becomes are real problem when several thousand records need to be processed.
The layout of the Data and formulas is as per the example below with the Purple text showing Data and the Blue text indicating formula's:
<table border="0" cellpadding="0" cellspacing="0" width="1234"><col style="width: 48pt;" width="64"> <col style="width: 88pt;" width="117"> <col style="width: 86pt;" width="115"> <col style="width: 42pt;" width="56"> <col style="width: 38pt;" width="50"> <col style="width: 48pt;" span="13" width="64"> <tbody><tr style="height: 33.75pt;" height="45"> <td class="xl25" style="height: 33.75pt; width: 48pt;" height="45" width="64">TICKETID</td> <td class="xl25" style="border-left: medium none; width: 88pt;" width="117">Start</td> <td class="xl25" style="border-left: medium none; width: 86pt;" width="115">Finish</td> <td class="xl25" style="border-left: medium none; width: 42pt;" width="56">Priority</td> <td class="xl25" style="border-left: medium none; width: 38pt;" width="50">Status</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Service Hours Type</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Mon</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Tue</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Wed</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Thu</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Count of PH on Fri</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Start Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Middle Hours</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">End Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Bank Hols Hrs</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">Same Day Elapsed</td> <td class="xl27" style="border-left: medium none; width: 48pt;" width="64">SLAHOLD Time</td> <td class="xl26" style="border-left: medium none; width: 48pt;" width="64">Elapsed Time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt; border-top: medium none; width: 48pt;" height="17" width="64">IN0002</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 88pt;" align="right" width="117">Thu 21/04/2011 12:00</td> <td class="xl29" style="border-top: medium none; border-left: medium none; width: 86pt;" align="right" width="115">Tue 31/05/2011 17:00</td> <td class="xl28" style="border-top: medium none; border-left: medium none; width: 42pt;" align="right" width="56"> 2</td> <td class="xl30" style="border-top: medium none; border-left: medium none; width: 38pt;" width="50">CLOSED</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">3</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">0</td> <td class="xl32" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">1</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">8:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">338:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">11:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">16:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">0:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">0:00:00</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 48pt;" align="right" width="64">373:00:00</td> </tr> </tbody></table>
The Formula to Count Public Holidays occurring on a Monday is as follows:
=IF(C2<B2,"Open or Error",IF(INT(C2)-INT(B2)<=1,0,IF(WEEKDAY('Service Hours'!$L$4)=2,IF(AND('Service Hours'!$L$4>=INT(B2)+1,'Service Hours'!$L$4<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$5)=2,IF(AND('Service Hours'!$L$5>=INT(B2)+1,'Service Hours'!$L$5<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$6)=2,IF(AND('Service Hours'!$L$6>=INT(B2)+1,'Service Hours'!$L$6<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$7)=2,IF(AND('Service Hours'!$L$7>=INT(B2)+1,'Service Hours'!$L$7<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$8)=2,IF(AND('Service Hours'!$L$8>=INT(B2)+1,'Service Hours'!$L$8<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$9)=2,IF(AND('Service Hours'!$L$9>=INT(B2)+1,'Service Hours'!$L$9<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$10)=2,IF(AND('Service Hours'!$L$10>=INT(B2)+1,'Service Hours'!$L$10<=INT(C2)-1),1,0))+IF(WEEKDAY('Service Hours'!$L$11)=2,IF(AND('Service Hours'!$L$11>=INT(B2)+1,'Service Hours'!$L$11<=INT(C2)-1),1,0))))
The approach to the Elapsed Time calculation is as follows:
a - If the Start and End time is on the same day a single calculation is performed
b - If the Start and End times are on different days the calculation is accomplished in four parts as follows:
1 - Work out any hours on Start day
2 - Work out any hours on End day
3 - Work out any hours in the middle i.e not Start or End plus deduct Public Holiday count
4 - Work out any hours occurring on a Public Holiday
c - The Working Hours to use for each ticket is decided based on Priority with an appended number to represent days Sun to Sat & Public Hols
I have example data and formulas in a workbook but wasn't sure how to attach it to this post.
I'd appreciate any suggestions as to how this could be improved either by by changing the logic / approach or suggested Formula changes or even using User Defined Functions.
Currently using Excel 2003.
Thanks in advance
Ian
