+ Reply to Thread
Results 1 to 2 of 2

Calculating NETWORKINGDAYS in hours, subtracting lunch hour

  1. #1
    Registered User
    Join Date
    03-08-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Calculating NETWORKINGDAYS in hours, subtracting lunch hour

    I'm currently making a worksheet to calculate the working days between two dates and times, however I'm running into a problem when attempting to deduct lunch break hours. I've split the formula into 2 parts for this post: the first part , the second part deducts hours taken for lunch.
    • [@Start] = Start date & time in MM/DD/YY HH:MM Format
    • [@End] = End date & time in MM/DD/YY HH:MM Format
    • Lunch_Break_Start = Lunch hour start time in HH:MM Format
    • Lunch_Break_End = Lunch hour End time in HH:MM Format


    The first part of the formula (checks if End date is blank, calculates NETWORKINGDAYS (in hours) between 2 dates):
    =IF([@End]="","",((NETWORKDAYS([@Start],[@End])-1)*(Daily_End_Time-Daily_Start_Time)+IF(NETWORKDAYS([@End],[@End]),MEDIAN(MOD([@End],1),Daily_End_Time,Daily_Start_Time),Daily_End_Time)-MEDIAN(NETWORKDAYS([@Start],[@Start])*MOD([@Start],1),Daily_End_Time,Daily_Start_Time)))

    The second part continues from the first part:
    -IF((MOD([@End],1)<Lunch_Break_Start),((NETWORKDAYS([@Start],[@End])-1)*(Lunch_Break_end-Lunch_Break_Start)),((NETWORKDAYS([@Start],[@End]))*(Lunch_Break_end-Lunch_Break_Start)))

    Together, it looks like this:
    =IF([@End]="","",((NETWORKDAYS([@Start],[@End])-1)*(Daily_End_Time-Daily_Start_Time)+IF(NETWORKDAYS([@End],[@End]),MEDIAN(MOD([@End],1),Daily_End_Time,Daily_Start_Time),Daily_End_Time)-MEDIAN(NETWORKDAYS([@Start],[@Start])*MOD([@Start],1),Daily_End_Time,Daily_Start_Time)))-IF((MOD([@End],1)<Lunch_Break_Start),((NETWORKDAYS([@Start],[@End])-1)*(Lunch_Break_end-Lunch_Break_Start)),((NETWORKDAYS([@Start],[@End]))*(Lunch_Break_end-Lunch_Break_Start)))

    It all seems to work correctly, UNLESS the end date is on 3/31/12 and the end time is in the AM. It is adding an extra hour to the duration when this happens (177:00 instead of 176:00). Additionally, after adding the second half of the formula, the initial 'check if @End is blank' step fails for blank cells, and #VALUE! is displayed. I've attached the file, as well.
    ResourceDuration.xlsx
    Any help on this would be greatly appreciated! Thank you.

  2. #2
    Registered User
    Join Date
    07-02-2015
    Location
    HONG KONG
    MS-Off Ver
    2010
    Posts
    1

    Re: Calculating NETWORKINGDAYS in hours, subtracting lunch hour

    This function seems can work in excel function, but can it be apply in a VBA code in excel?
    Because when i applyed marco recording in this function, it has some error.

    May you upload a VBA code version of this function? Thank you very much

+ 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