Calculating Dates Excluding Weekends

Question

I am creating some scheduling of dates for a task in SharePoint Online. The user enters the first date and a number of days it will take to complete the work. I have created a calculated column which subtracts the number of days from the Finish Date and populates the date to start, such as =[Finish Date]-[No of Days]

However I want it to calculate only weekdays. This is the equation I've created, but I can't get it to work. Any ideas?

=IF(WEEKDAY([Finish Date]-[No of Days])=1,([Finish Date]-[No of Days])-2, IF(WEEKDAY([Finish Date]-[No of Days])=7,([Finish Date]-[No of Days])-1,([Finish Date]-[No of Days])))

Answer 1

Try this formula

=IF(WEEKDAY([Finish Date]-(FLOOR([No of Days]/5,1)*7+MOD([No of Days],5)-1))=1,[Finish Date]-(FLOOR([No of Days]/5,1)*7+MOD([No of Days],5)-1)-2,IF(WEEKDAY([Finish Date]-(FLOOR([No of Days]/5,1)*7+MOD([No of Days],5)-1))=7,[Finish Date]-(FLOOR([No of Days]/5,1)*7+MOD([No of Days],5)-1)-1,[Finish Date]-(FLOOR([No of Days]/5,1)*7+MOD([No of Days],5)-1)))

Answer 2

This formula adds two days to a start date column but looks to whether the start date falls on either a Thursday (5th day) or Friday (6th day) to add 2 or 1 days respectively.

=([Start Date])+2+IF(OR(WEEKDAY([Start Date])=5,WEEKDAY([Start Date])=6),2,0)+IF(WEEKDAY([Start Date])=7,1,0)