+ Reply to Thread
Results 1 to 2 of 2

remove NETWORKDAYS function from formula

  1. #1
    Registered User
    Join Date
    02-25-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    remove NETWORKDAYS function from formula

    I have been using the formula below to calculate the total hours run time within a certain time range (12:00PM - 8:00PM) for each of our units. This calculates only the hours run within the time range whether the unit ran for one hour or multiple days. The issue I need help with is that the formula only calculates workdays (NETWORKDAYS) and does not include weekends. We are now running our units seven days a week and I need assistance changing the formula to calculate seven days a week. I have been struggling with this for a couple of days now.

    A2= START TIME
    B2= END TIME
    A5= START DATE
    B5= END DATE

    =IF(OR($B$2 < $A$2,B5 < A5),0,(NETWORKDAYS(A5,B5)-(NETWORKDAYS(A5,A5)*IF(MOD(A5,1) > $B$2,1,(MAX($A$2,MOD(A5,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS(B5,B5)*IF(MOD(B5,1) < $A$2,1,($B$2-MIN($B$2,MOD(B5,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)

    Below is an example. The unit ran from Friday at noon until Monday at 9:00PM. The total hours calculated within the time range (12:00pm - 8:00PM) should be 32 but since the formula is not capturing weekend runs I receive a total of 16.

    START TIME
    12:00 PM

    END TIME
    8:00 PM

    START DATE
    FRI 11/3/17 12:00 PM

    END DATE
    MON 11/6/17 9:00PM

    NET WORK HOURS (DECIMAL)
    16.00

    NET WORK HOURS (H:MM)
    16:00
    Last edited by kern630; 02-21-2017 at 01:06 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,175

    Re: remove NETWORKDAYS function from formula

    Try

    =IF(OR($B$2 < $A$2,B5 < A5),0,(NETWORKDAYS.INTL(A5,B5,"0000000")-(NETWORKDAYS.INTL(A5,A5,"0000000")*IF(MOD(A5,1) > $B$2,1,(MAX($A$2,MOD(A5,1))-$A$2)/($B$2-$A$2)))-(NETWORKDAYS.INTL(B5,B5,"0000000")*IF(MOD(B5,1) < $A$2,1,($B$2-MIN($B$2,MOD(B5,1)))/($B$2-$A$2))))*($B$2-$A$2)*24)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. NETWORKDAYS Function Help
    By awsachsen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-09-2014, 12:33 PM
  2. Excel 2007 : NETWORKDAYS function
    By souravbajaj in forum Excel General
    Replies: 1
    Last Post: 03-25-2010, 01:20 PM
  3. Networkdays function
    By syberian in forum Excel General
    Replies: 1
    Last Post: 02-28-2010, 03:47 PM
  4. NETWORKDAYS function?
    By Darin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2006, 09:45 AM
  5. Networkdays function help
    By MattG in forum Excel General
    Replies: 2
    Last Post: 05-07-2006, 03:25 PM
  6. [SOLVED] Nesting Networkdays function inside and If function
    By Addison in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-13-2006, 03:10 PM
  7. [SOLVED] NETWORKDAYS FUNCTION, Help please
    By BiggyTwo in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-29-2006, 09:45 PM

Tags for this Thread

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