Excel IFS Function With Dates: Examples and Date Rules

Formulas & functions · Updated November 19, 2026

Yes. Excel’s IFS function can test dates just like other values: compare a date cell with TODAY(), another date, or a date created with DATE(), then return a label or value for the first condition that is true. For example, with a due date in A2, =IFS(A2<TODAY(),"Overdue",A2=TODAY(),"Due today",A2>TODAY(),"Upcoming") classifies it relative to today; the sections below explain date ranges, blank cells, times, and other common wrinkles.

What IFS Does With Dates

IFS checks logical tests from left to right and returns the value paired with the first test that evaluates to TRUE. Its pattern is IFS(logical_test1,value_if_true1,[logical_test2,value_if_true2],...). The tests can use date comparisons such as less than, greater than, or equal to. The return values may be text labels, numbers, dates, or other formulas.

Excel stores genuine dates as serial numbers and displays them using a date format. This means a comparison such as A2<TODAY() is a numeric comparison under the hood, not a comparison of how the dates look on screen. A cell can display “May 18, 2026” or “18-May-26” and still compare correctly if Excel recognizes its content as a date.

IFS is available in Microsoft 365 and Excel 2019, Excel 2021, and Excel 2024. Earlier Excel releases may not support it. IFS is useful when a result depends on several ordered conditions; for a single test, a regular IF formula is usually simpler.

Classify a Date as Overdue, Due Today, or Upcoming

Suppose A2 holds a task’s due date. This formula assigns one of three status labels based on the current date:

With a due date in A2, enter =IFS(A2<TODAY(),"Overdue",A2=TODAY(),"Due today",A2>TODAY(),"Upcoming"). If A2 is yesterday, the result is “Overdue”; if it is today, the result is “Due today”; if it is tomorrow, the result is “Upcoming.” TODAY() returns the current date, without a time component.

The tests cover dates before, equal to, and after today, so any genuine date value will match one of them. But an empty A2 is often treated like zero in a date comparison and may be labeled overdue. If blanks should have their own status, test for them first.

With a due date in A2 and blank cells meant to be reported as missing, use =IFS(A2="","Missing date",A2<TODAY(),"Overdue",A2=TODAY(),"Due today",A2>TODAY(),"Upcoming"). For an empty A2 the result is “Missing date”; for dates, the remaining tests classify them as before, on, or after today. Put the blank test first so it takes priority.

Use IFS to Assign Date Ranges

A common use is to place each date into a period, such as a year, service window, or reporting band. Create boundaries as dates rather than typing date strings into the formula. DATE(year,month,day) constructs a date using numeric arguments and avoids many regional-format ambiguities.

With the date to classify in A2, use =IFS(A2<DATE(2026,1,1),"Before 2026",A2<DATE(2027,1,1),"2026",TRUE,"2027 or later"). If A2 contains May 18, 2026, the result is “2026.” The first test catches earlier dates; the second catches dates from January 1, 2026, up to but not including January 1, 2027; TRUE supplies the final category for dates on or after that boundary.

This pattern uses the first day of the next period as an exclusive upper boundary. That is often clearer than comparing with December 31, particularly if the data might include time values. For example, a date-time late on December 31 is still less than midnight on January 1, so it falls in the intended year.

For more categories, add another test-and-result pair in the right order. Write down the intended boundaries first, and check that the ranges have no gaps or overlaps. IFS does not automatically detect a missing range: if no test is TRUE and no catch-all condition is present, Excel returns #N/A.

Build Deadline Windows and Aging Bands

IFS can also turn deadlines into practical action groups, such as overdue, due within a week, or later. A catch-all TRUE at the end handles anything that did not match an earlier test. The order matters: put the most specific or earliest boundary first, then broaden the remaining conditions.

With a due date in A2, and “within 7 days” intended to include today and the next seven calendar days, use =IFS(A2="","No date",INT(A2)<TODAY(),"Overdue",INT(A2)<=TODAY()+7,"Due within 7 days",TRUE,"Later"). If the date is blank the result is “No date”; if it is today, two days from today, or exactly seven days from today, the result is “Due within 7 days”; a later date returns “Later.”

INT removes any time fraction from an Excel date-time value, so the formula treats an item due later today as due today rather than as a future day. It also groups dates by calendar day. If time of day is important—for example, a delivery due at 3 p.m.—do not remove the time; compare the full date-time values and define the boundaries accordingly.

For aging bands, use the same logic with thresholds such as 30, 60, and 90 days. Decide whether the boundary day belongs in the lower or higher band, then use < versus <= consistently. Explicit boundary choices prevent a record from being placed in an unexpected category.

Why Condition Order and a Final TRUE Matter

IFS stops at the first TRUE test. A broad condition placed too early can therefore hide more precise conditions that follow it. For example, if a first test asks whether a date is after January 1 and a later test asks whether it is after July 1, every July date already satisfies the first test. Put the July test first, or define mutually exclusive date intervals.

A final TRUE is the IFS equivalent of a default outcome. It is not mandatory when earlier tests cover every possible input, but it is a useful safeguard when the formula should return a label rather than #N/A. A default should be meaningful, such as “Later,” “Other,” or “Check date,” rather than silently hiding unexpected data.

When a range has a lower and upper boundary, it is often easiest to test only the upper boundaries in ascending order, then finish with TRUE. The earlier tests implicitly exclude dates already assigned to prior bands because IFS has returned a result as soon as one matches.

Enter and Fill the Formula Down a Column

In Microsoft 365 for Windows, select the cell where the status should appear, type =IFS followed by the tests and results, and press Enter. If the formula is in B2 and the date is in A2, copying or filling the formula down adjusts A2 to A3, A4, and so on. Keep boundary dates fixed in the formula with DATE(), or refer to cells containing the boundaries if you want to change them without editing the formula.

For a simple worksheet, copy the formula down with the fill handle at the selected cell’s lower-right corner. If the data is formatted as an Excel table, a formula entered in a table column typically fills the other rows in that calculated column. Format the source date column as a date if you want dates to display clearly; changing the display format does not convert text into a real date.

Formula argument separators can vary with Excel’s regional settings. The examples here use commas, as in the standard English-language syntax requested. If Excel rejects a formula copied from elsewhere, check whether that installation expects semicolons as separators, and confirm that the quotation marks are straight double quotes.

Common Date Errors and How to Fix Them

  • A date is stored as text. A value that looks like a date may not be a true Excel date, especially if it was imported or entered in a different regional format. A comparison may behave unexpectedly or return an error. Convert the source to real dates, or inspect a sample by changing its number format to General; a genuine date displays as a serial number.
  • A blank row is called overdue. Add a blank check such as A2=”” before the date tests. If the source contains formulas that return an empty string, this check generally catches that displayed blank too.
  • A date with a time is not equal to TODAY(). TODAY() is midnight at the start of the current day. A value such as today at 2 p.m. is greater than TODAY(), so a direct equality test fails. Use INT(A2)=TODAY() when the time should be ignored.
  • IFS returns #N/A. None of the tests matched. Add a final TRUE and a suitable default, or revise the tests so every valid input is covered.
  • A date boundary is off by one day. Decide whether the boundary is included, then use < or <= deliberately. Remember that a date-time value includes a fractional part, so a comparison with a date at midnight can treat later times that same day differently.
  • The displayed date changes but the result does not. Formatting affects appearance, not the underlying value. Check whether the input is genuinely a date and whether the formula is comparing the intended cell.

IFS Compared With IF and Lookup Tables

Use IFS when the rules are a manageable set of ordered conditions and each condition has a clear result. It is easier to read than deeply nested IF formulas because each test-result pair appears in sequence. IF remains a good choice for one condition with two outcomes, such as “past due” versus “not past due.”

For many date bands that business users may revise, a small boundary table can be easier to maintain than a long formula. A lookup approach can keep the thresholds and labels in worksheet cells, while IFS keeps them embedded in the formula. Whichever approach you choose, document whether each threshold is inclusive and test dates immediately before, on, and after each boundary.

IFS does not make date comparisons inherently more accurate than IF. Both rely on the underlying values being valid dates and on the logic being correctly ordered. Choose based on readability and how often the rules will change, not because one function changes how Excel stores dates.

Frequently Asked Questions

Can I use IFS with dates in Excel?
Yes. Compare date cells with operators such as <, <=, =, >=, and >, or create boundary dates with DATE(). IFS returns the result for the first condition that is TRUE.

How do I compare a date with today?
Use TODAY() in the logical test. For a due date in A2, A2<TODAY() checks whether it is before today. If A2 may contain a time, use INT(A2) when you want to compare calendar days rather than exact times.

Why does my IFS date formula return #N/A?
Every logical test evaluated as FALSE, and there was no matching default. Add a final TRUE condition with a result such as “Other,” or adjust the boundaries so they cover the intended date range.

Can IFS use dates typed directly into the formula?
Prefer DATE(year,month,day), such as DATE(2026,1,1), over a quoted date string. Quoted strings can be interpreted differently depending on regional date settings, while DATE uses numeric year, month, and day arguments.

Does IFS ignore the time in a date-time cell?
No. Excel date-time values include a time fraction, so direct comparisons can distinguish different times on the same day. Use INT(date_cell) in the tests if the rule should consider only the calendar date.

What happens if the date cell is blank?
A blank can behave like zero in comparisons and be classified as an old date. Add a blank test before the other conditions, and decide whether truly empty cells and formulas returning an empty string should receive the same label.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.