+ Reply to Thread
Results 1 to 4 of 4

calculating business hour including saturday error result

Hybrid View

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    3

    calculating business hour including saturday error result

    Hello, i have the formula to calculate between two dates but the result is uncorrect or error if i put the start/ time out of business hour.
    Please help me and hope get solutions...
    This is my formula

    =(INT(I11)-INT(H11)-INT((WEEKDAY(H11-1)+INT(I11)-INT(H11))/7)-SUMPRODUCT((J2:J3>H11)*(J2:J3<I11)*(WEEKDAY(J2:J3)<>1)))*(I2-H2)+MOD(I11,1)-MOD(H11,1)

    Explanation :
    I11 = EndDate
    H11 = StartDate
    J2:J3 = HolidayList
    I2 = EndTime
    H2 = StartTime

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,712

    Re: calculating business hour including saturday error result

    Can the start end times/dates be any time or date, e.g. could the start time be on a holiday or the end time on a Sunday?
    Audere est facere

  3. #3
    Registered User
    Join Date
    06-10-2013
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: calculating business hour including saturday error result

    Thx DLL for your respon, i've many read your solutions
    Yes, sometimes i get End/StartDate out of business hour. Here i more explain my format so you can understand what i means...
    all format :
    End/StartDate = in different cell, mm/dd/yyyy hh:mm:ss
    End/StartTime = in different cell, 07:30 --------> 20:00
    HolidayList = mm/dd/yyyy
    result = [h]:mm

    So, for example data is 06/23/2013 21:00 in End/StartDate, i got the result become #######, formulas can't read time out of business hour...
    meanwhile, if End/StartDate is in business hour, formulas is working
    Expect you know the answer for me, thx a lot DLL

  4. #4
    Registered User
    Join Date
    06-10-2013
    Location
    Jakarta
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: calculating business hour including saturday error result

    also if is in holiday,sunday

+ 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