SharePoint: How to add workdays only to a date


In working on a project tracking list, I needed to come up with formula that took a start date and added weeks to it. But I didn’t want it to count weekend days.

In Excel you can use the WORKDAY formula, but this is not supported in SharePoint. So after some research, which led me to this post: https://sharepoint.stackexchange.com/questions/60292/calculated-date-column-to-take-into-account-working-days, I came up with this modified formula for a SharePoint calculated column:

=IF(WEEKDAY(Start + Weeks*7,2)=6,Start + Weeks*7+2,IF(WEEKDAY(Start + Weeks*7,2)=7,Start + Weeks*7+1,Start + Weeks*7)-3)

This formula has the following assumptions & notes:
1) Start date is a Monday
2) Weeks is a whole number
3) It doesn’t take into account holidays
4) You can’t use a calculated field in a SharePoint calendar view

Unknown's avatar

About Jason Sherry

I am a ~30 year Exchange consultant and expert. I currently work for Commvault as a Solutions Specialist for Microsoft Infrastructure For more info see my resume at: http://resume.jasonsherry.org
This entry was posted in Microsoft, O365, SharePoint, Technical and tagged . Bookmark the permalink.

Leave a comment