1

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])))
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)))
  • works for me! Thanks – Tally Mar 12 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.