Others

How to Calculate Complete Days Since Creation in a SharePoint List

Phi Hung Vo Sep 25, 2026 869 views

Question details

The user needs a reliable formula to accurately calculate the number of full days that have elapsed since an item was generated in a SharePoint list.

How to Calculate Complete Days Since Creation in a SharePoint List

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Creating or modifying a calculated column in a SharePoint list to track item age based on the default Created date field.
Observed behavior: Using the standard formula TODAY()-Created returns incorrect or unexpected results because TODAY() defaults to midnight, whereas the Created field contains a specific time value.

Before you start

Ensure you have the appropriate site owner or list administrator permissions to add or edit calculated columns in the target SharePoint list.

Solution 1Recommended

Use the DATEDIF Formula with NOW()

This solution guarantees the exact calculation of full days elapsed by comparing the exact item creation time with the current exact time.

Using TODAY() can cause calculation discrepancies because it automatically assumes a time of 12:00 AM (midnight) for the current day. By replacing it with NOW(), the formula accurately factors in the current time of day against the precise time the item was originally created.

Access List Settings

Navigate to your target SharePoint list, click the gear icon in the top right corner of the screen, and select 'List settings'.

Create or Edit a Column

Scroll down to the Columns section. Click 'Create column' to make a new one, or click on an existing calculated column's name to edit it.

Input the DATEDIF Formula

In the formula box, type exactly: =DATEDIF(Created,NOW(),"d")

Set Data Type and Save

Choose 'Number' as the data type returned from this formula, set the number of decimal places to 0, and click 'OK' at the bottom of the page to save your changes.

Accuracy Guaranteed: The 'd' parameter in the DATEDIF function strictly forces the result to return complete, full 24-hour days only, preventing fractional day rounding errors.

Free Microsoft Office alternative

Handle Exported SharePoint Data Easily with WPS Office

While SharePoint lists are part of the Microsoft 365 ecosystem, you can seamlessly export your list data and perform advanced date calculations using WPS Spreadsheet. WPS Office is a highly compatible, free, and lightweight alternative to Microsoft Office.

1. Export List Data: In your SharePoint list command bar, click 'Export' and choose 'Export to Excel' or 'Export to CSV'.
2. Open in WPS Spreadsheet: Launch WPS Office on your computer and open your newly exported data file.
3. Apply Date Formulas: Use formulas like =DATEDIF(A2, NOW(), "d") directly within your WPS Spreadsheet cells to analyze and track item ages effortlessly.

Free and lightweight complete Office suiteFully compatible with Microsoft Excel (.xlsx and .csv) formatsSupports advanced date formulas like DATEDIF, NOW(), and TODAY() nativelyFamiliar user interface with zero learning curve for easy migration

Frequently Asked Questions

Why does TODAY() minus Created give the wrong number of days in SharePoint?

The TODAY() function returns the current date but assumes the time is exactly midnight (12:00 AM). Because the Created column stores both the date and the specific time the item was generated, subtracting them directly often causes fractional discrepancies or drops a day prematurely.

Can I use DATEDIF to calculate months or years in a SharePoint list?

Yes, you can modify the last parameter of the DATEDIF formula to calculate different time units. Use "m" to return complete months and "y" to return complete years elapsed since the exact creation date.

How do I ensure my calculated column updates automatically every day?

Calculated columns in SharePoint lists typically only recalculate when an item is created or modified by a user. To force daily refreshes for formulas relying on NOW() or TODAY(), you will need to set up a scheduled flow in Power Automate to routinely update the list items.

Office Quick Solutions