How to Fix Negative Days Overdue (#NUM Error) in Microsoft Lists Calculated Columns
Question details
The user needs to calculate and display negative days overdue in a Microsoft Lists calculated column without triggering a #NUM! error.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Creating a calculated column to find the exact difference in days between an estimated completion date and today's date, where the estimated completion date is sometimes in the future.
- Observed behavior
- The DATEDIF function returns a #NUM! error when attempting to calculate a negative date interval, specifically when the target completion date is later than the current date.
Ensure you have Edit or Owner permissions for the Microsoft List, and note the exact internal name of your date column (e.g., [Est Completion date]) before applying formulas.
Use Conditional Logic to Handle Future Dates
Bypass the #NUM error by using an IF function to check if the date is in the future, then manually appending a minus sign to a reversed DATEDIF calculation.
Since the standard DATEDIF formula cannot process negative intervals, you must reverse the start and end dates for future deadlines and format the output with a '-' symbol as a text string.
Open your Microsoft List, click the gear icon (Settings) at the top right corner of the page, and select 'List settings'.
Scroll down to the 'Columns' section and click on your calculated column name to edit it, or click 'Create column' if you are making a new one.
In the Formula box, enter the following logic: =IF(LEN([Est Completion date])=0,0,IF([Est Completion date]<TODAY(),DATEDIF([Est Completion date],TODAY(),"d"),"-"&DATEDIF(TODAY(),[Est Completion date],"d"))). Replace '[Est Completion date]' with your exact column name.
Under 'The data type returned from this formula is', select 'Single line of text'. This is necessary because the formula outputs a minus sign as a string. Click 'OK' to save your changes.
Use Power Automate to Refresh Date Calculations
Calculated columns utilizing 'TODAY()' do not automatically recalculate every day. Use Power Automate to keep the days overdue continuously accurate.
Manage Complex Date Trackers with WPS Spreadsheet
While Microsoft Lists is useful for basic team tracking, managing complex date formulas and automated recalculations is often much easier in a dedicated spreadsheet tool. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. With WPS Spreadsheet, you can easily handle advanced DATEDIF formulas natively, avoid text-conversion sorting issues, and enjoy a seamless, familiar user experience.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Export Microsoft List: In your Microsoft List, click 'Export' and select 'Export to Excel' to download your current tracker data.
- 3. Manage Dates Seamlessly: Open the exported file in WPS Spreadsheet to easily apply mathematical subtraction to dates, keeping them as true numbers for proper sorting.
Frequently Asked Questions
Why does DATEDIF return a #NUM! error for future dates?
The DATEDIF function calculates the elapsed time between a start date and an end date. If the specified start date is mathematically greater than the end date (like comparing today to a future deadline), DATEDIF cannot resolve a negative interval and returns a #NUM! error.
Will the TODAY() function in my Microsoft List calculate automatically every day?
No. In Microsoft Lists and SharePoint, calculated columns are evaluated only when a list item is initially created or manually updated. Simply viewing the list on a new day does not trigger the TODAY() function to refresh.
Is there a way to sort the negative days properly in Microsoft Lists?
When using the text-based workaround for negative days, proper numerical sorting is compromised because the column must be set to 'Single line of text'. To achieve proper sorting, you would need to calculate the days overdue using a Power Automate flow that outputs a standard negative integer into a standard Number column.
