For the purpose of calculating Incident Resolution performance, I’m attempting to calculate the work hour’s duration between two dates & times (i.e. Total work hours between ‘Start Date & Time of Incident’ to ‘End Date & Time of Incident’). I’m using Excel 2007.
There appears to be a great variety of methods to calculate work hours between two dates, though I’m struggling to handle the inclusion of Saturday work hours (i.e. 08:00-13:00) for some Site types (see below), and also public holidays. I’d appreciate advise of an appropriate method & excel formula/s to best handle this requirement.
Based on the following criteria:
There are 3 different Site Types, where incident duration time is determined by the Site type.
Type Z Site = 24/7 (Mon-Sun, including Public Holidays)
Type Y site = Mon-Fri (excluding Public Holidays)
YStartDay = 08:00
YEndDay = 18:00
Type X Site = Mon-Sat (excluding Public Holidays)
XStartDay = 08:00 (Start of work day Mon-Sat)
XEndDay = 18:00 (End of work day Mon-Fri)
XSEndDay = 13:00 (End of work day for Sat only)
Format of Start and End Date & Time = ddd dd/mm/yyyy hh:mm
Format of Calculated Incident duration = [h]:mm
Example of data:
Site Type Start Date & Time End Date & Time Work Hours Duration
X Mon 24/12/2012 12:48 Wed 02/01/2013 17:47
X Tue 01/01/2013 19:23 Wed 02/01/2013 1:36
X Fri 04/01/2013 17:10 Fri 04/01/2013 20:45
Y Fri 04/01/2013 23:10 Mon 07/01/2013 10:45
X Thu 10/01/2013 18:18 Fri 18/01/2013 17:49
Z Fri 11/01/2013 23:10 Mon 14/01/2013 10:45
Z Wed 16/01/2013 11:41 Wed 16/01/2013 12:50
Y Sat 26/01/2013 16:25 Sat 26/01/2013 16:30
Y Sat 26/01/2013 18:55 Sun 27/01/2013 7:12
Z Sat 26/01/2013 23:59 Sun 27/01/2013 11:23
Y Sun 27/01/2013 23:28 Tue 29/01/2013 12:21
Y Sun 27/01/2013 23:28 Tue 29/01/2013 12:21
HolidayList
25/12/2012
26/12/2012
1/01/2013
28/01/2013
Regards
JReacher
Bookmarks