SharePoint Document Issues

How to Fix SharePoint List Formula for Calculating Elapsed Hours

Elise Williams Oct 1, 2026 869 views

Question details

The user needs to fix a SharePoint calculated column formula that is intended to calculate elapsed hours using the NOW() function and the Created field, but is failing for newly added list items.

Fixing a SharePoint List Formula for Calculating Elapsed Hours

Product: Microsoft SharePoint
Device & OS: not provided
Scenario: Calculating the elapsed time in hours since a list item was generated using a calculated column.
Observed behavior: The formula fails to calculate the elapsed hours correctly for the newest list item when relying on the Created field.

Before you start

Verify that your SharePoint site time zone settings match your local computer time zone settings exactly, as time discrepancies will cause calculation errors when using the NOW() function.

Solution 1Recommended

Replace the 'Created' Field with 'Modified' in Your Formula

Update the calculated column to use the Modified field instead of the Created field to ensure the elapsed time calculation runs successfully for new items.

Calculated columns in SharePoint can sometimes struggle to process the 'Created' field immediately after a new item is added. By switching the reference to 'Modified', the formula resolves more reliably for new entries.

Keep in mind that because the 'Modified' timestamp changes every time a user edits the list item, this solution will reset the elapsed time upon any item update.

Access List Settings

Navigate to your SharePoint list, click the gear icon for Settings, and select 'List settings'.

Edit the Calculated Column

Scroll down to the Columns section and click on the calculated column that is failing to compute the elapsed hours.

Update the Formula

In the formula box, replace all instances of 'Created' with 'Modified'. Enter the following formula: =IF(INT(NOW()-Modified)<1,0,INT(NOW()-Modified)*24)+IF(HOUR(NOW()-Modified)<1,0,HOUR(NOW()-Modified))+MINUTE(NOW()-Modified)/60

Save Changes

Click 'OK' at the bottom of the page to save your updated column settings. Create a new test item to verify the calculation is working.

Replace the 'Created' Field with 'Modified' in Your Formula

Important Consideration on Editing: Because the 'Modified' field updates on every edit, use an alternative method like Power Automate if your calculation must strictly preserve the original creation time.

Solution 2

Use Power Automate for Persistent Elapsed Time Calculations

If you require the calculation to always use the original creation time without resetting when users edit the item, use a Power Automate flow instead of a calculated column.

Free Microsoft Office alternative

Enhance Your Document Productivity with WPS Office

While resolving complex SharePoint formula issues, simplify your offline spreadsheet and data calculation tasks with WPS Office. It provides a lightweight, highly compatible alternative to Microsoft Office, ensuring your data analysis is smooth and efficient.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Export SharePoint Data: Export your SharePoint list data to an Excel (.xlsx) file and open it locally in WPS Spreadsheet.
3. Apply Formulas Offline: Use standard Spreadsheet functions to calculate time differences reliably without server-side syncing limitations.

Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formatsFree, lightweight, and easy to install on multiple devicesRich built-in formulas and functions for spreadsheet calculationsFamiliar user interface allowing for a seamless transition from Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the NOW() function work accurately with the Created field in SharePoint calculated columns?

SharePoint calculated columns are processed immediately upon item creation. Sometimes, the server's commit timing causes the NOW() function and the Created timestamp to mismatch slightly on a brand new item, causing the formula to fail or return unpredictable results until the item is modified.

Do SharePoint calculated columns update automatically in real-time as hours pass?

No. SharePoint calculated columns do not continuously update in the background. The NOW() function only recalculates when the list item is explicitly created or edited by a user.

How do I prevent the elapsed time from resetting when a user edits the list item?

If you use the 'Modified' field in your formula, any edits will naturally reset the timer. To prevent this while tracking elapsed time from creation, avoid using calculated columns and instead use a Power Automate flow to calculate and push the elapsed time into a standard number column.

Document & File Issues