How to Fix Excel Formula That Sets a Value to Zero After One Year
Question details
The user needs to correct an Excel formula intended to return 0 when a date is over a year old, which currently produces a #NAME? error.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating attendance or tracking points where a penalty or value expires and resets to zero after exactly one year from a recorded date.
- Observed behavior
- The current formula uses invalid syntax involving the TODAY function without parentheses, resulting in a #NAME? error instead of returning the expected numerical value.
Ensure the cells containing your target dates are properly formatted as 'Date' rather than 'Text'. Verify that your spreadsheet software is set to calculate formulas automatically.
Use the EDATE and TODAY Functions to Compare Dates
The most reliable way to check if a date is exactly one year old is by using the EDATE function to subtract 12 months from the current date.
Subtracting 365 from a year calculation often causes errors like #NAME? or inaccuracies due to leap years. The EDATE function cleanly handles month-based date math and evaluates accurately.
Click on the cell where you want the formula result to appear.
Type the formula: `=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(A4<=EDATE(TODAY(),-12),0,"")))`
Press Enter. This nested IF statement checks if the date in cell A4 is less than or equal to exactly 12 months before today's date.
Click the bottom-right corner of the cell and drag the fill handle down to apply this updated formula to the rest of your column.

Fix the Syntax Error in the Original Formula
If you prefer modifying your original YEAR logic, you must ensure all functions have proper parentheses to avoid the #NAME? error.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet provides a seamless experience for writing, debugging, and executing complex formulas like EDATE and nested IF statements. It handles advanced date calculations effortlessly.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the attendance or date records.
- 2. Select the formula cell: Click on the cell where you want to apply the one-year expiration formula.
- 3. Input the EDATE function: Type `=IF(A4<=EDATE(TODAY(),-12),0,"")` directly into the formula bar.
- 4. Apply and drag: Press Enter to instantly calculate the result, then drag the fill handle down to apply it to all other rows.

Frequently Asked Questions
Why does my Excel formula show a #NAME? error?
A #NAME? error means the spreadsheet program doesn't recognize text in the formula. This frequently happens if a function name is misspelled or if a volatile function like TODAY is missing its required empty parentheses, e.g., written as TODAY instead of TODAY().
What does the EDATE function do in Excel?
The EDATE function returns the serial number of a date that is a specific number of months before or after a starting date. For example, EDATE(TODAY(), -12) calculates the exact date one year prior to the current day.
Can I use DATEDIF instead of EDATE to check if a year has passed?
Yes, the DATEDIF function can calculate the difference between two dates in years, months, or days. However, using EDATE combined with TODAY() is often more straightforward and widely supported when establishing a dynamic one-year cutoff threshold in an IF statement.




