+ Reply to Thread
Results 1 to 5 of 5

Time Calculations(Cont...)

  1. #1
    Registered User
    Join Date
    03-19-2011
    Location
    Riyadh,Saudia Arabia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Time Calculations(Cont...)

    In an earlier post daddylonglegs showed a great way of calculating time differences accommodating Working hours exclusive of weekends, would it be possible to change the weekdays taken into account e.g Saturday- Wednesday

    The below link shows how to exclude specific days of the week without networkdays but does not calculate for working hours

    http://www.cpearson.com/excel/betternetworkdays.aspx






    Originally Posted by daddylonglegs
    I do have a formula that accomodates start and/or end times outside the business hours, i.e.

    =(NETWORKDAYS(A2,B2)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),J$3, J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),J$3,J$2)

    Where A2, B2, J2 and J3 are as before. This formula doesn't take lunch into account, do you need that?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Time Calculations(Cont...)

    Given your revised working days (Sat - Wed) covers the same number of days as NETWORKDAYS default (Mon - Fri) you can simply adjust/offset the dates in the NETWORKDAYS calculation so as to account for move from Mon-Fri to Sat-Wed

    Adapting daddylonglegs' formula from above:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-19-2011
    Location
    Riyadh,Saudia Arabia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Time Calculations(Cont...)

    Hi


    Thanks for the quick response, using your provided formula i am getting the output

    4:30:20

    for below dates although its a Friday on the 18th and my weekdays are from Sat-Wed, it should be zero, any suggestions?

    Start_Date End Date
    3/18/2011 3:59 3/18/2011 12:30

    J2=08:00
    J3=16:00

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Time Calculations(Cont...)

    With Start_Date & End Date in A2 & B2 respectively and start/end hours in J2 & J3 as outlined the formula should generate 0.

    Please post a sample file to illustrate your result.

  5. #5
    Registered User
    Join Date
    03-19-2011
    Location
    Riyadh,Saudia Arabia
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Time Calculations(Cont...)

    IT Works!!!

    Apologies for the confusion, tried it on my colleagues system as mine was acting up, got the dreaded blue screen and just crashed, you're a lifesaver!

+ 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