Results 1 to 2 of 2

Thread: Update NETWORKDAYS Formula to ignore negative numbers???

  1. #1

    Update NETWORKDAYS Formula to ignore negative numbers???



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone,

    So I have what I think is a fairly easy to answer question, I just dont think for a novice its easy to spot lol.
    Im currently tracking some metrics for our helpdesk tickets. One of these metrics calculates the ticket lifetime and displays the amount of time. However, Currently it breaks if there is a blank cell, And I get a negative number.

    How can I update my formula to display a 0 rather then a negative number?

    Formula used is below as well as a screenshot to help visually.

    =(NETWORKDAYS(C39+D39,K39+L39)*work_hours_in_day)-IF(WEEKDAY(C39+D39,2)>=6,0,MIN(MAX(24*(MOD(C39+D39,1)-work_start_time),0),work_hours_in_day)) - IF(WEEKDAY(K39+L39,2)>=6,0,MIN(24*MAX((work_end_time-MOD(K39+L39,1)),0),work_hours_in_day))
    Click image for larger version. 

Name:	TicketTrackingFormula.png 
Views:	15 
Size:	57.9 KB 
ID:	2887



    Help me... Please? lol Im sure its just a matter of inserting another "IF(jialsRidiculouslyLongFormula<0" but I cant narrow down Were lol

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    You can add an IF statement to check if the Date Closed is blank?

    Try:

    =IF(K39="",0,(NETWORKDAYS(C39+D39,K39+L39)*work_hours_in_day)-IF(WEEKDAY(C39+D39,2)>=6,0,MIN(MAX(24*(MOD(C39+D39,1)-work_start_time),0),work_hours_in_day)) - IF(WEEKDAY(K39+L39,2)>=6,0,MIN(24*MAX((work_end_time-MOD(K39+L39,1)),0),work_hours_in_day)))


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •