+ Reply to Thread
Results 1 to 3 of 3

Calculate work hours between two dates/times, incl Sat (variable hours) & excl Pub Hols

  1. #1
    Registered User
    Join Date
    03-02-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Calculate work hours between two dates/times, incl Sat (variable hours) & excl Pub Hols

    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

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculate work hours between two dates/times, incl Sat (variable hours) & excl Pub Hol

    Hi,

    Can I suggest that you upload the actual workbook and add some examples of results that you expect with notes as to how you have arrived at those results.

    In my experience people prefer to work with the workbook the poster is actually using rather than a narrative description of it. This avoids misunderstandings.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-02-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Calculate work hours between two dates/times, incl Sat (variable hours) & excl Pub Hol

    Thanks Richard. Sample small data file attached.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1