Formula Errors

How to Create an Excel Formula to Display YES or NO for a 12-Week KPI

Maira Mehtab Sep 20, 2026 869 views

Question details

A user needs a formula in column C to automatically display YES or NO, depending on whether the response date in column B is within 12 weeks of the application date in column A.

Product: Excel
Device & OS: not provided
Scenario: Tracking operational KPIs by measuring the date difference between a submitted application and its subsequent response.
Observed behavior: The user requires the correct syntax for the formula and is encountering a #NAME? error with their initial attempt.

Before you start

Ensure that the data in both the application date column (A) and the response date column (B) are properly formatted as 'Date' values rather than text.

Solution 1Recommended

Use the IF Function to Calculate the 12-Week Date Difference

Write a straightforward IF statement that subtracts the application date from the response date and evaluates if the difference is 84 days (12 weeks) or less.

In Excel, dates are stored as sequential serial numbers, meaning you can easily calculate the difference between two dates using simple subtraction. Since 12 weeks equals exactly 84 days (12 weeks x 7 days), the formula only needs to check if the difference is less than or equal to 84.

Select the target cell

Click on cell C2, which is the first cell in column C where you want the YES or NO result to appear.

Enter the IF formula

Type the formula `=IF((B2-A2)<=84, "YES", "NO")` into the formula bar.

Apply to the entire column

Press Enter to view the result for the first row. Then, click and drag the fill handle (the small square at the bottom-right of the cell) down column C to apply the formula to your remaining data.

Handling Empty Cells: If some rows do not have a response date yet, you can prevent false outputs by nesting another IF statement: `=IF(B2="", "Pending", IF((B2-A2)<=84, "YES", "NO"))`.

Solution 2

Troubleshoot the #NAME? Error

Resolve the #NAME? error by correcting typographical errors in your function names or adjusting your regional separator settings.

Track KPIs efficiently

Calculate Date KPIs with WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet environment for all your data analysis and KPI tracking needs, including conditional logic and complex date calculations.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your application and response dates.
2. Input the KPI formula: Select the target cell in column C and enter `=IF((B2-A2)<=84, "YES", "NO")`.
3. Confirm the result: Press Enter to instantly generate the YES or NO output based on the 12-week threshold.
4. Drag to fill: Use the smart fill handle to drag the formula down, automatically calculating the KPI for all rows.

100% compatible with Microsoft Excel formulas and date functionsBuilt-in formula evaluation tool to quickly spot and fix errorsLightweight application that runs smoothly on almost any deviceFree comprehensive templates for project and KPI tracking

microsoft office alternative - wps office

Frequently Asked Questions

How many days should I use in my formula for a 12-week KPI?

Since there are 7 days in a week, a 12-week timeframe is exactly 84 days. You should use the number 84 in your formula to represent the 12-week threshold.

Why does my date formula return a negative number or strange error?

This usually happens if the response date is earlier than the application date, or if the cells are formatted as General text instead of Dates. Ensure both columns A and B are strictly set to the 'Date' format.

Can I use the DATEDIF function for this KPI calculation?

Yes, you can use `=IF(DATEDIF(A2, B2, "d")<=84, "YES", "NO")`. However, simply subtracting the earlier date from the later date (B2-A2) is more concise and yields the exact same result.

What should I do if my formula still shows an error after typing it exactly as shown?

Check your system's regional settings. If your region uses commas as decimal separators, Excel formulas will require semicolons (;) instead of commas to separate the arguments. Replace the commas in the formula with semicolons.

Excel & Data Problems