Microsoft Lists Formula to Calculate Days Until an End Date
Question details
The user needs a calculated column formula in Microsoft Lists to determine the number of days from a start date to today, applying the calculation only if the end date column is blank or set in the future.
- Product
- Microsoft Lists
- Device & OS
- not provided
- Scenario
- Setting up a project tracker where ongoing task durations need to be measured, but past or completed items should not show an active day count.
- Observed behavior
- The user wants a blank output when the end date has already passed, while maintaining an accurate running day count for ongoing tasks.
Verify the exact display names of your start date and end date columns in Microsoft Lists, as any names containing spaces must be enclosed in square brackets (e.g., [Start Date]) within your formula.
Use Nested IF and OR Functions for Conditional Date Calculation
Implement a combination formula using IF, OR, ISBLANK, and TODAY() to evaluate the end date status before calculating the day difference.
This formula checks if the end date is either empty or in the future. If either condition is true, it proceeds to subtract the start date from today's date. If the end date has already passed, it forces the cell to remain blank.
Navigate to your Microsoft List, click on 'Add column' at the end of your headers, and select 'Calculated' from the dropdown menu.
In the formula box, paste the following logic: =IF(OR(ISBLANK([COLUMN 2]),[COLUMN 2]>TODAY()),IF(ISBLANK([COLUMN 1]),"",TODAY()-[COLUMN 1]),""). Replace [COLUMN 1] with your exact start date column name and [COLUMN 2] with your end date column name.
Under 'The data type returned from this formula is', select 'Number'. Set the number of decimal places to 0, since you are calculating whole days.
Click 'Save' at the bottom. Add a test row with a past end date and another with a future end date to verify the calculated column accurately displays blanks and active day counts respectively.
Manage Projects and Date Tracking Easily with WPS Spreadsheet
If you need advanced task management and find Microsoft Lists formulas restrictive, WPS Spreadsheet provides a robust, easy-to-use alternative. You get full access to advanced date formulas like DATEDIF, NETWORKDAYS, and TODAY, making project tracking seamless.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet or choose a project tracking template.
- 2. Set Up Your Columns: Label your columns (e.g., Column A for Start Date, Column B for End Date) and format them as Dates.
- 3. Apply Spreadsheet Formula: In Column C, use standard Excel logic: =IF(OR(ISBLANK(B2),B2>TODAY()), IF(ISBLANK(A2),"",TODAY()-A2), "") and press Enter.
- 4. Drag to Auto-Fill: Click the bottom-right corner of the formula cell and drag downward to apply this conditional logic to all your project rows instantly.
Frequently Asked Questions
Why does my Microsoft Lists formula return a syntax error?
Syntax errors generally occur due to misspelled column names, missing square brackets around column names with spaces, or using comma separators instead of semicolons (depending on your regional settings). Double-check your exact column system names.
Does the TODAY() function in Microsoft Lists update every day automatically?
Calculated columns utilizing the TODAY() function in Microsoft Lists typically only recalculate when an item is created or updated. To force daily updates, users often rely on creating a daily scheduled flow in Power Automate.
Can I calculate only business days between dates in Microsoft Lists?
Microsoft Lists does not have a native NETWORKDAYS function like Excel or WPS Spreadsheet. Calculating strictly working days requires a much more complex formula factoring in weekends, or relying on a Power Automate flow to calculate the duration.
