• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Network days calculation issue

KEvinG

New Member
HI i have 2 columns of data which is created date and resolved date both formatted as dd-mm-yyyy-h:mm the MTTR column is formatted as dd:hh:mm and the formula used is =(NETWORKDAYS(R146,S146)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(S146,S146),MEDIAN(MOD(S146,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(R146,R146)*MOD(R146,1),"17:00","8:00") the calculation made does not seem to provide an accurate MTTR in days hours mins. See example attatched, I also have a yes/no formula based on specific time values if the MTTR cell is less than or greater than say 10 minutes or xx hours. again cant seem to get itworking correctly. any help greatly appreciated.
 

Attachments

Chihiro

Excel Ninja
You can't use NETWORKDAYS for business hour calculation. As the formula name suggests, it ignores hours and only calculates number of days.

What is your expected result? And what's your calculation parameters?
 

KEvinG

New Member
Thanks for this, I am trying to get the number of days hours/mins value where the created date and resolved date. The calculation does seem to work for values where it is the same day it seems to lose its accuracy when its more than day and weekend etc. What would you advise as an alternative to network days to get a precise calculation

regards ?
 
Top