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

  • I've done something similar before but only for weekdays - didn't cover non-working time and holidays and thats going to be very difficult if not impossible to do in SharePoint calculated columns.

    Tuesday, June 15, 2010 2:07 PM
  • I don't know how to exclude holidays but i exclude weekends(Sat&Sun)-

    IF(ISERROR(DATEDIF([Start Date],[End Date],”d”)),””,(DATEDIF([Start Date],[End Date],”d”))+1-INT(DATEDIF([Start Date],[End Date],”d”)/7)*2-IF((WEEKDAY([End Date])-WEEKDAY([Start Date]))<0,2,0)-IF(OR(AND(WEEKDAY([End Date])=7,WEEKDAY([Start Date])=7),AND(WEEKDAY([End Date])=1,WEEKDAY([Start Date])=1)),1,0)-IF(AND(WEEKDAY([Start Date])=1,(WEEKDAY([End Date])-WEEKDAY([Start Date]))>0),1,0)-IF(AND(NOT(WEEKDAY([Start Date])=7),WEEKDAY([End Date])=7),1,0))



    • Proposed as answer by us1983 Tuesday, June 23, 2015 9:55 AM
    Tuesday, March 03, 2015 6:01 AM
  • can you please give the formula to exclude Friday & Saturday?
    Monday, March 16, 2015 12:29 PM