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

### Question

• 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 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.

http://blog.pentalogic.net/2008/11/working-days-weekdays-holidays-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))

-Thanks

Swapnil

• Proposed as answer by 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