Calculate Date/Time Difference in a Calculated Column Omitting Weekends, Holidays and Non-Working Hours


  • I am looking for a formula to use in a calculated column that calculates the difference between two date/time fields while excluding weekends, holidays and non-working hours

    6/1/2010 9:43 AM - 6/7/2010 1:45 PM

    Here is the closest that I have gotten; however, this formula gives me a result of 96:

    =IF(AND((WEEKDAY([Resolved Date],2))<(WEEKDAY(Created,2)),((WEEKDAY(Created,2))-(WEEKDAY([Resolved Date],2)))>1),(((DATEDIF(Created,[Resolved Date],"D")))-(FLOOR((DATEDIF(Created,[Resolved Date],"D")+1)/7,1))-3),(((DATEDIF(Created,[Resolved Date],"D")))-(FLOOR((DATEDIF(Created,[Resolved Date],"D")+1)/7,1)*2))*24)

    Any and all assistance is greatly appreciated!

    • Moved by Mike Walsh FIN Tuesday, June 15, 2010 7:43 AM dvwp q (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    Monday, June 14, 2010 10:28 PM

All replies