+ Reply to Thread
Results 1 to 3 of 3

Excel formula Difference 2 dates,include w/e's,exclude hols,variable business hours

  1. #1
    Registered User
    Join Date
    07-06-2011
    Location
    Lancashire
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Excel formula Difference 2 dates,include w/e's,exclude hols,variable business hours

    Need formula adapting or alternative to include variable business hours at weekend and exclude holidays that can also occur at weekend e.g. New Years Day.

    Formula should also allow for variable business hours each day during week but are same week to week and exclude holidays.

    The formula below does NOT allow for variable business hours that do occur.

    =((NETWORKDAYS(StartTime,EndTime,HolidayList)-1)*(BusHrsEnd BusHrsStart)+IF(NETWORKDAYS(EndTime,EndTime,HolidayList),MEDIAN(MOD(EndTime,1), BusHrsEnd, BusHrsStart), BusHrsEnd)-MEDIAN(NETWORKDAYS(StartTime,StartTime,HolidayList)*MOD(StartTime,1), BusHrsEnd, BusHrsStart))))

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

    Re: Excel formula Difference 2 dates,include w/e's,exclude hols,variable business hou

    Hello JammyDodger

    If you have start date/time in A2, end date/time in B2 and holidays listed in H2:H12 you can use this formula

    =SUMPRODUCT(INT((WEEKDAY(A2-K$2:K$8)+INT(B2)-INT(A2))/7),N$2:N$8)-SUMPRODUCT((H$2:H$12>=INT(A2))*(H$2:H$12<B2),LOOKUP(WEEKDAY(H$2:H$12),K$2:N$8))-IF(COUNTIF(H$2:H$12,INT(B2)),0,MEDIAN(0,LOOKUP(WEEKDAY(B2),K$2:M$8)-MOD(B2,1),LOOKUP(WEEKDAY(B2),K$2:N$8)))-IF(COUNTIF(H$2:H$12,INT(A2)),0,MEDIAN(0,MOD(A2,1)-LOOKUP(WEEKDAY(A2),K$2:L$8),LOOKUP(WEEKDAY(A2),K$2:N$8)))

    format result cell as [h]:mm

    where K2:N8 contains a table defining the business hours for each day of the week

    see attached. Columns A and B contain randomly generated time periods in 2011 - press F9 to re-generate

    Note that K2:K8 contains numbers 1 to 7 formatted as dddd to show the days of the week.....but the formula references the numbers so this shouldn't be changed

    Edit: modified post
    Attached Files Attached Files
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-06-2011
    Location
    Lancashire
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Excel formula Difference 2 dates,include w/e's,exclude hols,variable business hou

    Excellent, problem fixed and should help loads of other people as well.

+ 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