How to Calculate Days Between Dates in a SharePoint Calculated Column
Question details
The user needs a way to calculate the total number of occupied days between a Start Date and End Date in a SharePoint list, with a fallback to the current date if the End Date is blank.
- Product
- Microsoft SharePoint
- Device & OS
- not provided
- Scenario
- Creating a calculated column to track duration, occupancy, or service time in a SharePoint list based on two specific date fields.
- Observed behavior
- The user wants a dynamic numerical output representing the difference in days between two date columns, specifically handling current residents by utilizing the current date when the End Date field is empty.
Ensure you have Site Owner or List Administrator permissions to create and manage columns in your SharePoint list. Verify that your 'Start Date' and 'End Date' columns already exist and are strictly formatted as Date/Time fields.
Use an IF Statement with the TODAY Function
Combine the IF function with the TODAY function to dynamically calculate the days between dates, ensuring accurate counts even when the End Date is left blank.
In SharePoint, basic subtraction between two date columns will yield the difference in days. However, when an End Date is missing (e.g., for current residents), the calculation will return an error or invalid number. Wrapping the logic in an IF statement resolves this by substituting the blank value with today's date.
Navigate to your target SharePoint list. Click the 'Gear' icon in the top right corner and select 'List settings', then click 'Create column' under the Columns section.
Enter a name for your new column, such as 'Days Occupied', and select 'Calculated (calculation based on other columns)' as the column type.
In the Formula box, input the following formula: =IF(ISBLANK([End Date]), TODAY-[Start Date], [End Date]-[Start Date]). Ensure the column names exactly match your list.
Under 'The data type returned from this formula is', select 'Number'. Set the number of decimal places to '0', then click 'OK' to save your new calculated column.
Calculate Days Within a Specific Month Boundary
Use boundary conditions combined with the MAX and MIN functions to count only the days that fall within a specific reporting month.
Boost Your Document Productivity with WPS Office
While SharePoint handles your list calculations online, WPS Office is the perfect lightweight companion for seamlessly editing and analyzing your exported SharePoint list data. As a powerful, free alternative to Microsoft Office, it offers full compatibility and robust formula support without the heavy subscription costs.
- 1. Export your SharePoint List: From your SharePoint list view, click 'Export' in the top command bar and choose 'Export to Excel' to download your data file.
- 2. Open with WPS Spreadsheets: Launch WPS Office and open the downloaded .xlsx or .csv file. The formatting and data types from SharePoint will remain fully intact.
- 3. Perform Advanced Calculations: Utilize WPS Spreadsheets' powerful built-in functions to perform complex date calculations that bypass SharePoint's calculated column limitations.
Frequently Asked Questions
Why is the TODAY function not updating every day in my SharePoint calculated column?
In SharePoint, calculated columns are only evaluated and recalculated when a list item is initially created or subsequently modified. To achieve a dynamically updating daily count, you would need to use JSON view formatting or set up a daily scheduled Power Automate flow to update a standard number column.
How do I calculate working days only instead of total calendar days in SharePoint?
SharePoint calculated columns do not support a built-in NETWORKDAYS function like Excel does. To calculate working days, you must use a highly complex nested IF formula to subtract weekends, or more practically, use a Power Automate flow to calculate working days and write the final value to your list.
Can I format the numerical result of my calculated days column?
Yes. When creating the calculated column, select 'Number' as the return type and specify the number of decimal places (usually 0 for whole days). If you want to append text like 'days' after the number, you can either concatenate it in the formula (e.g., =[Your Formula] & " days") or use SharePoint JSON column formatting for better visual presentation.
