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))
Help me... Please? lol Im sure its just a matter of inserting another "IF(jialsRidiculouslyLongFormula<0" but I cant narrow down Were lol
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)))
Bookmarks