+ Reply to Thread
Results 1 to 4 of 4

Calculate difference in date/time excluding non-working hours and Thurs/Fri weekends

  1. #1
    Registered User
    Join Date
    12-22-2010
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2007
    Posts
    4

    Calculate difference in date/time excluding non-working hours and Thurs/Fri weekends

    Hello! I am trying to create a formula to measure the time in hours/mins) between 2 dates/times, excluding weekends and only counting between 9am - 5pm on a workday.

    =(NETWORKDAYS(C2,D2,Holiday_List)-1)*(Day_End-Day_Start)+IF(NETWORKDAYS(D2,D2,Holiday_List),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS(C2,C2,Holiday_List)*MOD(C2,1),Day_End,Day_Start)
    Where:
    C2 = Start/Create Date/Time
    D2 = End/Assigned Date/Time

    This formula works perfectly assuming that Saturday and Sunday are the only weekend days. however in other parts of the world the weekend can be Friday/Saturday or Thursday/Friday so I need a formula that can account for this.

    I have tried this one but it does not work:
    =SUMPRODUCT(INT((WEEKDAY(A2-F$2:F$8)+INT(B2)-INT(A2))/7),H$2:H$8-G$2:G$8)-MOD(A2,1)+MOD(B2,1)-LOOKUP(WEEKDAY(B2),F$2:H$8)+LOOKUP(WEEKDAY(A2),F$2:G$8)
    where:
    A2 = start date / time
    B2 = end date / time
    C2 = difference
    F2..F8 = weekday 1 Sun - 7 Sat
    G3..G8 = start times for Mon-Sat
    H3..H8 = end times for Mon-Sat


    How can I measure the difference between dates and times so that a work day is from 9am - 5pm and the weekend can be changed based on world region?

    I hope that you can help!!

  2. #2
    Valued Forum Contributor ffffloyd's Avatar
    Join Date
    05-20-2008
    Location
    Melbourne, VIC, Australia
    MS-Off Ver
    Office 365
    Posts
    243

    Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

    Is the "weekend" always two consecutive days?

    Maybe add an offset to each of the dates you work with so that they become skewed across the Western world's idea of a weekend.

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

    Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

    As ffffloyd says, you could use an "OFFSET" if the weekend is always 2 consecutive days, e.g. for Friday/Saturday weekend

    =(NETWORKDAYS(C2+1,D2+1,INDEX(Holiday_List+1,0))-1)*(Day_End-Day_Start)+IF(NETWORKDAYS(D2+1,D2+1,INDEX(Holiday_List+1,0)),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS(C2+1,C2+1,INDEX(Holiday_List+1,0))*MOD(C2,1),Day_End,Day_Start)

    or for Excel 2010 only you can use NETWORKDAYS.INTL function like this

    =(NETWORKDAYS.INTL(C2,D2,7,Holiday_List)-1)*(Day_End-Day_Start)+IF(NETWORKDAYS.INTL(D2,D2,7,Holiday_List),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS.INTL(C2,C2,7,Holiday_List)*MOD(C2,1),Day_End,Day_Start)

    In the latter 7 denotes a Fri/Sat weekend, change to 6 for Thu/Fri, 5 for Wed/Thu etc. In the first formula +1s would change to +2 for Thu/Fri etc.
    Audere est facere

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

    Dear DaddyLonglegs,

    I was just going through the posts of interest after a very long time..and this was something I was wanting to do...as well..
    Calculate the Time Invested for Each Client for a Chartered Accountant...

    Is it ok If I may ask of a few things in this thread as its already been solved...?

    Regards
    e4excel

+ 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