Formula Errors

Excel Nested IF Formula to Label Dates Early, Late, or Blank

Olivia MillerOlivia Miller Nov 12, 2026 870 views

Question details

Create a formula that compares two dates and displays Early when the date in Q9 is before P9, Late when it is after P9, or a blank result when Q9 is empty.

Excel Nested IF Formula for Early, Late, or Blank Dates
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user needs to classify a date in one column against a reference date in another column.
Observed behavior
The desired result is Early for an earlier date, Late for a later date, and blank when the date to compare has not been entered.
Before you start

Confirm that P9 and Q9 contain real Excel dates rather than date-like text. Enter the formula in a result cell, such as R9, and then fill it down for additional rows.

Solution 1Recommended

Use a nested IF formula that checks for a missing date first

Use this formula when Q9 is the date being compared and equal dates should return a blank result.

The first IF prevents Excel from evaluating the comparison when Q9 is blank. The remaining IF statements compare Q9 with the reference date in P9.

1
Select the result cell

Click the cell where you want the status to appear, for example R9.

2
Enter the nested IF formula

Type or paste =IF(Q9="","",IF(Q9<P9,"Early",IF(Q9>P9,"Late",""))) into the formula bar, then press Enter.

3
Copy the formula to other rows

Select the formula cell and drag the fill handle down, or double-click the fill handle, to apply the comparison to the rest of the column.

Use a nested IF formula that checks for a missing date first
Formula result: This formula returns Early when Q9 is before P9, Late when Q9 is after P9, and blank when Q9 is blank or the two dates are the same.
WPS Spreadsheets

Compare Dates with Nested IF Formulas in WPS Spreadsheets

WPS Spreadsheets supports the same IF and date-comparison formulas, so you can classify records as Early, Late, or blank and continue working with existing Excel files.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the P and Q date columns.
  2. 2. Select the status column cell: Click the first output cell, such as R9, where the Early or Late label should appear.
  3. 3. Enter the date comparison formula: Type =IF(Q9="","",IF(Q9<P9,"Early",IF(Q9>P9,"Late",""))) in the formula bar and press Enter.
  4. 4. Apply the formula to all rows: Drag the fill handle from the formula cell down the status column to calculate the remaining entries.
Use nested IF formulas to compare dates and return custom labels.Open, edit, and save Microsoft Excel .xlsx and .xls files.Apply formulas quickly with a familiar spreadsheet interface.Fill date-comparison formulas down an entire column in a few clicks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return a blank when both dates are the same?

In =IF(Q9="","",IF(Q9<P9,"Early",IF(Q9>P9,"Late",""))), neither Q9<P9 nor Q9>P9 is true for equal dates, so the final result is blank.

How can I mark equal dates as Early?

Use =IF(Q9="","",IF(Q9<=P9,"Early","Late")). The <= operator returns Early when Q9 is earlier than or equal to P9.

What should I do if Excel does not compare my dates correctly?

Select the date cells, open Home > Number Format, and choose a Date format. If values remain left-aligned or do not calculate correctly, convert the date-like text to real dates first.

Can I copy the nested IF formula to every row?

Yes. Enter the formula in the first result cell, then drag or double-click the fill handle at the lower-right corner of the cell to fill the formula down.