Formula Errors

How to Fix Excel Formula That Sets a Value to Zero After One Year

Elise WilliamsElise Williams Nov 11, 2026 868 views

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.

How to Fix an Excel Formula That Sets a Value to Zero After One Year
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the formula result to appear.

2
Enter the EDATE formula

Type the formula: `=IF(B4="Absent",1,IF(OR(B4="Tardy",B4="Left Early"),0.33,IF(A4<=EDATE(TODAY(),-12),0,"")))`

3
Execute the calculation

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.

4
Apply to other rows

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.

Use the EDATE and TODAY Functions to Compare Dates
Formula Breakdown: EDATE(TODAY(), -12) dynamically calculates the exact date one year ago today, ensuring accurate evaluations regardless of leap years or irregular month lengths.
Advanced Spreadsheet Tool

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. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the attendance or date records.
  2. 2. Select the formula cell: Click on the cell where you want to apply the one-year expiration formula.
  3. 3. Input the EDATE function: Type `=IF(A4<=EDATE(TODAY(),-12),0,"")` directly into the formula bar.
  4. 4. Apply and drag: Press Enter to instantly calculate the result, then drag the fill handle down to apply it to all other rows.
100% compatibility with Microsoft Excel (.xlsx) formulas, functions, and formatting.Built-in formula error checking helps you easily spot missing parentheses or invalid syntax like #NAME?.Comprehensive function library including advanced date, time, and logical functions.
microsoft office alternative - wps office

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.