Sharepoint list: How can I calculate difference between two dates without Fridays and Saturdays

Question

I need a formula that can calculate difference between 2 dates without calculating Fridays and Saturdays.

I need to use this formula to create a calculated column in list.

Thank you

Answer 1

Please try as following:

IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1,WEEKDAY([Start Date],2)<>7,WEEKDAY([Start Date],2)<>6),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),(WEEKDAY([Start Date],2)=7),(WEEKDAY([End Date],2)<>6)),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Date],2))=(WEEKDAY([Start Date],2)),(OR((WEEKDAY([Start Date],2)=7),(WEEKDAY([Start Date],2)=6)))),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Date],2))>(WEEKDAY([Start Date],2)),(WEEKDAY([End Date],2)=6)),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-1),IF(AND((WEEKDAY([End Date],2))>(WEEKDAY([Start Date],2)),(WEEKDAY([End Date],2)=7),((WEEKDAY([End Date],2))-(WEEKDAY([Start Date],2)))<6),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),((DATEDIF([Start Date],[End Date],"D")+1)-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))))))

Or this one:

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))

Reference:

https://social.technet.microsoft.com/Forums/lync/en-US/f978790b-af9f-4238-b64f-c6e2b8a24dbc/forum-faq-using-calculated-column-to-calculate-days-between-two-dates-excluding-weekends?forum=sharepointgeneral