'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate a SharePoint Due Date Based on Working Days
Question details
The user needs to calculate a list item's due date based on a 'Sent' date and 'Additional Information' criteria, while exclusively counting working days (skipping weekends).
- Product
- SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column in a SharePoint List to generate due dates without counting Saturdays and Sundays.
- Observed behavior
- SharePoint calculated columns do not support Excel's native WORKDAY function, making it difficult to automatically exclude weekends without a complex workaround.
Verify the exact names of the columns in your SharePoint list (such as 'Sent' or 'Additional Information'), as your formula references must match them precisely to work.
Use a Nested IF Formula with the WEEKDAY Function
Use this workaround logic to evaluate the day of the week and manually add the correct number of days to skip the weekend.
Because SharePoint calculated columns lack a true WORKDAY function, the standard approach is to use the WEEKDAY function. This allows you to check which day of the week the 'Sent' date falls on and conditionally add extra days to bridge across the weekend.
Navigate to your SharePoint List settings, select the existing calculated column for your due date, or click 'Create column' and choose 'Calculated (calculation based on other columns)'.
In the formula box, input the conditional logic to evaluate the 'Sent' date. For example: =IF([Additional Information]="Class 1", IF(WEEKDAY(Sent)=4, Sent+5, IF(WEEKDAY(Sent)=5, Sent+4, IF(WEEKDAY(Sent)=6, Sent+3, Sent+2))), IF(WEEKDAY(Sent)=4, Sent+5, IF(WEEKDAY(Sent)=5, Sent+4, IF(WEEKDAY(Sent)=6, Sent+3, Sent+5))))
Check your SharePoint site's regional settings. In some locales, SharePoint requires semicolons (;) instead of commas (,) to separate function arguments. Update the punctuation in the formula if necessary, then click 'OK' to save.
Manage Your Trackers Easily with WPS Spreadsheet
While SharePoint lists require complex workarounds for simple date calculations, managing your trackers in WPS Spreadsheet lets you use the built-in WORKDAY function to instantly calculate due dates. As a free, lightweight alternative to Microsoft Office, WPS Office provides a highly familiar UI, seamless migration, and perfect compatibility with standard formats.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet to act as your project or task tracker.
- 2. Enter Your Data: Input your 'Sent' dates and task classifications into your spreadsheet columns.
- 3. Use the WORKDAY Function: In the Due Date column, simply type =WORKDAY(start_date, days) to automatically calculate the deadline while seamlessly skipping weekends and optionally custom holidays.
Frequently Asked Questions
Why doesn't the WORKDAY function work in SharePoint calculated columns?
SharePoint lists do not support the full library of spreadsheet functions available in Excel. Specifically, the WORKDAY and NETWORKDAYS functions are missing from SharePoint, which requires users to build custom nested IF logic using the WEEKDAY function instead.
What does WEEKDAY(Sent)=4 mean in the SharePoint formula?
In SharePoint's default WEEKDAY function, Sunday is 1, Monday is 2, Tuesday is 3, and Wednesday is 4. The formula checks if the sent date is a Wednesday (4) to add extra days so the resulting deadline safely skips the upcoming weekend.
How do I fix a syntax error when saving my SharePoint formula?
Syntax errors in SharePoint calculated columns are often caused by regional settings. If your region uses a comma as a decimal separator (like in many European countries), SharePoint requires you to use semicolons (;) instead of commas (,) to separate formula arguments.
