SharePoint Document Issues

How to Make a SharePoint Calculated Column Count Same Start and End Date as One Day

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to create a SharePoint calculated column that inclusively counts the total days between a start and end date, explicitly outputting '1 Day' when the two dates are identical.

Product: SharePoint
Device & OS: not provided
Scenario: Calculating an inclusive duration between two date columns within a SharePoint list.
Observed behavior: Standard date difference formulas return 0 days for identical dates, but the user requires an inclusive calculation that counts the same day as 1 full day.

Before you start

Verify the exact internal names of your start and end date columns in your SharePoint list, and ensure you have sufficient permissions to create or modify list columns.

Solution 1Recommended

Use an Inclusive DATEDIF Formula with an IF Condition

Apply a customized DATEDIF formula that adds 1 to the result for an inclusive count, paired with an IF statement to format the output grammatically as '1 Day' or 'X Days'.

The standard DATEDIF function calculates the strict interval between two dates, returning 0 if the dates are the same. By adding 1 to the function's result, the calculation becomes inclusive. Using an IF statement ensures that singular and plural outputs are handled correctly based on the final count.

Navigate to List Settings

Open your SharePoint list, click the Gear icon in the top right corner to access settings, and select 'List settings'.

Create a Calculated Column

Scroll down to the Columns section and click 'Create column'. Enter a recognizable name for your new column and select 'Calculated (calculation based on other columns)' as the column type.

Enter the Formula

Paste the following formula into the calculation box: `=IF(DATEDIF([Start Date],[End Date],"d")+1=1,"1 Day",DATEDIF([Start Date],[End Date],"d")+1&" Days")`. Be sure to replace `[Start Date]` and `[End Date]` with the actual names of your columns.

Set the Data Type

Under the formula box, choose 'Single line of text' as the data type returned from this formula, then click 'OK' at the bottom of the page to save your changes.

Regional Format Adjustments: Depending on your SharePoint site's regional settings, you may need to use semicolons instead of commas to separate the arguments in the formula (e.g., `=IF(DATEDIF([Start Date];[End Date];"d")+1=1;...`).

Free Microsoft Office alternative

Manage Data Easily with WPS Office Spreadsheets

While SharePoint lists are highly useful for web-based tracking, complex date calculations and data management are often much simpler in a dedicated spreadsheet application. WPS Office offers a free, lightweight, and highly compatible alternative to Microsoft Excel for all your data tracking needs.

1. Download and Install: Get WPS Office from the official website and easily install it on your PC or Mac.
2. Open WPS Spreadsheets: Launch the application and create a new workbook, or open your exported SharePoint list data.
3. Use Built-In Date Formulas: Utilize seamless Excel-compatible formulas to calculate differences between dates with ease and precision.

Completely free to use with robust spreadsheet capabilitiesHighly compatible with Microsoft Excel (.xlsx) formatsNative support for complex date functions including DATEDIFFamiliar user interface for a zero-learning-curve migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does my DATEDIF formula return a syntax error in SharePoint?

Syntax errors in SharePoint calculated columns typically occur due to misspelled column names or regional settings formatting. Ensure your column names exactly match your list structure inside the brackets, and try replacing commas with semicolons if your region dictates it.

Can I adapt this formula for custom column names?

Yes. If your date columns are named differently (e.g., 'Project Start' and 'Project End'), simply replace `[Start Date]` and `[End Date]` in the formula with your exact column names enclosed in square brackets.

How do I calculate only working days instead of total days in SharePoint?

To count only working days (excluding weekends), you must use a more complex formula utilizing the WEEKDAY function or implement a Power Automate flow, as SharePoint's default DATEDIF function does not natively exclude weekends.

Document & File Issues