+ Reply to Thread
Results 1 to 2 of 2

Turnaround Time, Including saturdays, start times outside business hours.

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Ottawa
    MS-Off Ver
    Excel 2002
    Posts
    1

    Turnaround Time, Including saturdays, start times outside business hours.

    Hello,

    I'm trying to figure out a formula to automatically calculate response times before we reply to emails we've received.

    A1 is the date and time we received the email
    B1 is the date and time we responded to the email.

    I need the turnaround time to include only hours when we are open and could respond. I'm not concerned about holidays at this point.

    From Monday to Friday the hours are 7am to 9pm, from Saturday it's only from 8am to 5pm. Closed on sundays. Keep in mind the start time could be any day, including after hours and sundays, but we would only count the turnaround time from when we next opened.

    I've tried searching online for answer, and have found so many different formulas, but none that seem to work in all cases.

    Thank you in advance for any help.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Turnaround Time, Including saturdays, start times outside business hours.

    Try this formula in C1

    =(SUMPRODUCT(INT((WEEKDAY(A1-{2,3,4,5,6,7})+INT(B1)-INT(A1))/7),{14,14,14,14,14,9})+MOD(B1,1)*24-IF(WEEKDAY(B1)=7,17,21)-MEDIAN(0,MOD(A1,1)*24-LOOKUP(WEEKDAY(A1),{1,2,7;0,7,8}),LOOKUP(WEEKDAY(A1),{1,2,7;0,14,9})))/24

    custom format C1 as [h]:mm
    Audere est facere

+ 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