How to Show Zero Instead of #NUM! for Future Date Progress in WPS Office Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that returns 0 instead of a #NUM! error when a DATEDIF-based progress formula references a future start date, while still capping progress at 100% after the end date.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A progress-tracking formula uses DATEDIF between a start date, today's date, and an end date to calculate completed progress.
- Observed behavior
- The formula returns a #NUM! error when the start date is in the future, instead of showing 0% progress.
Confirm that your start and end date cells contain real dates rather than text, and check whether your regional WPS Spreadsheets version uses commas or semicolons as formula separators.
Wrap the DATEDIF Progress Formula in IFERROR and Return Zero for Future Dates
Use this method when you want a single formula that eliminates the #NUM! error and automatically shows 0 before the start date and 100% after the end date.
The formula keeps the original DATEDIF calculation but adds two safety layers: IF checks whether TODAY() is before the start date, and IFERROR catches any remaining date-calculation errors.
Open the workbook in WPS Office, select the cell where the progress result should appear, and make sure the start date is in B2 and the end date is in C2, adjusting references as needed.
Type =IFERROR(IF(TODAY()<B2,0,MIN(1,(DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))),0) and press Enter. Use semicolons instead of commas if your regional WPS Spreadsheets settings require them.
Test with a start date in the future, today, and past. The cell should display 0 for future dates, a percentage between 0 and 100 for current periods, and 100 after the end date.
Use a Nested IF Formula Without IFERROR for More Visible Date Logic
Use this alternative if you prefer to separate the future, current, and completed cases explicitly rather than relying on error handling.
Fix Future Date Progress Errors in WPS Office Spreadsheets
Use WPS Office Spreadsheets to apply a single IFERROR/DATEDIF formula that replaces #NUM! with 0 for future periods and caps completed progress at 100%.
- 1. Open your Excel workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx file containing the DATEDIF progress formula.
- 2. Select the formula cell: Click the result cell, then paste the corrected formula =IFERROR(IF(TODAY()<B2,0,MIN(1,(DATEDIF(B2,TODAY(),"d")+1)/(DATEDIF(B2,C2,"d")+1))),0).
- 3. Check regional separators: If WPS Spreadsheets shows a formula error, replace commas with semicolons according to your regional settings.
- 4. Test the calculation: Enter future, current, and past start dates to confirm the cell displays 0, a percentage, and 100 respectively.
Frequently Asked Questions
Why does DATEDIF return #NUM! when the start date is in the future?
DATEDIF calculates the difference from an earlier start_date to a later end_date. If the start_date is later than the end_date, it cannot calculate a negative date difference and returns #NUM!. WPS Office Spreadsheets follows the same DATEDIF behavior.
Can I use this formula if my spreadsheet uses semicolons instead of commas?
Yes. Replace the comma argument separators with semicolons in the formula, for example =IFERROR(IF(TODAY()<B2;0;MIN(1;(DATEDIF(B2;TODAY();"d")+1)/(DATEDIF(B2;C2;"d")+1)));0).
What should I change if my dates are not in B2 and C2?
Replace the B2 and C2 cell references in the formula with your actual start date and end date cells. Make sure the same cell reference is updated everywhere it appears.
How do I show 100% instead of 1 after the period ends?
The formula returns the decimal value 1 for completed progress. Select the result cell, open the number format menu, and choose Percentage to display it as 100%.
Does this formula work in both Microsoft Excel and WPS Office?
Yes. WPS Office Spreadsheets supports DATEDIF and IFERROR, so the same formula works in both applications. Just adjust commas or semicolons to match your regional settings.
