'%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 Count Days from Start to Completion in SharePoint
Question details
The user needs a SharePoint calculated column formula to count the number of days between a start date and either today (for open tasks) or a completion date (for finished tasks), counting same-day completions as 1 day.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Tracking task duration in a SharePoint list by calculating the days between start and completion or the current date.
- Observed behavior
- SharePoint does not automatically calculate conditional date differences that handle both open and completed tasks while treating same-day completions as 1 day without a specific custom formula.
Ensure you have 'Date Started' and 'Date Completed' columns already created in your SharePoint list and that you have sufficient permissions to add and modify calculated columns.
Use a Calculated Column Formula in SharePoint
Apply a conditional IF and DATEDIF formula to dynamically calculate the days based on the task's completion status.
This solution uses SharePoint's native calculated column feature to check if the completion date is blank. If it is blank, it calculates the difference to TODAY(). If it is filled, it calculates the difference to the completion date, ensuring same-day tasks return 1 instead of 0.
Navigate to your SharePoint list, click on 'Add column', and select 'More...' at the bottom of the menu to open the classic column creation page.
Enter a column name (e.g., 'Days Open') and select 'Calculated (calculation based on other columns)' as the data type.
In the Formula box, paste the following code: =IF(AND(NOT(ISBLANK([Date Started])),ISBLANK([Date Completed])),DATEDIF([Date Started],TODAY(),"d"),IF(AND(NOT(ISBLANK([Date Started])),NOT(ISBLANK([Date Completed]))),IF([Date Started]=[Date Completed],1,DATEDIF([Date Started],[Date Completed],"d")),0))
Under 'The data type returned from this formula is', select 'Number'. Set the number of decimal places to 0, then click 'OK' to save.
Manage and Calculate Task Data Offline with WPS Office
While SharePoint is great for online lists, calculating massive datasets, applying complex date formulas, or managing offline schedules is often easier in a dedicated spreadsheet tool. WPS Office provides a powerful, free alternative to Microsoft Office, fully compatible with Excel files, allowing you to use advanced date formulas seamlessly.
- 1. Organize data in WPS Spreadsheet: Open WPS Spreadsheet and create your 'Start Date' and 'Completion Date' columns.
- 2. Apply the date formula: In a new column, enter the formula: =IF(B2="", DATEDIF(A2,TODAY(),"D"), IF(A2=B2, 1, DATEDIF(A2,B2,"D")))
- 3. Calculate all tasks: Press Enter and drag the fill handle down to apply the calculation to all your tasks instantly.
Frequently Asked Questions
Can I exclude weekends from the SharePoint day count?
Yes, but not easily with a standard calculated column formula, as SharePoint lacks a native NETWORKDAYS function. You will need to build custom logic using Power Automate or Power Apps to calculate business days.
Why does my SharePoint calculated column show a syntax error?
Syntax errors often occur due to regional settings. If your SharePoint region uses a comma as a decimal separator, you must replace the commas in the formula with semicolons (e.g., =IF(..., DATEDIF(...;"d"); ...)).
Does the DATEDIF function work in modern SharePoint lists?
Yes, the DATEDIF function is fully supported in both classic and modern SharePoint lists for calculating the difference between two dates in calculated columns.
How do I make the formula return 0 if the Start Date is empty?
The provided formula handles this implicitly, but for safety, you can wrap your entire formula in an IFERROR function or add a leading condition: =IF(ISBLANK([Date Started]), 0, [Your Formula]) to handle empty start dates.
