To compare a date in Excel with today, put the date in A2 and enter =IFS(A2<TODAY(),"Overdue",A2=TODAY(),"Due today",A2>TODAY(),"Upcoming"). IFS tests conditions in order and returns the result paired with the first condition that is true, so arrange date tests from the most specific or earliest boundary to the next one.
How IFS compares dates
Excel stores valid dates as numbers and displays those numbers using a date format. This means a comparison such as less than, equal to, or greater than works on the underlying date values: an earlier date is a smaller number, and a later date is a larger number. IFS does not have a special date-comparison mode; it evaluates ordinary logical tests involving date values.
The function takes condition-and-result pairs. Excel checks the first condition, then the second only if the first is false, and continues until it finds a true condition. It returns the result attached to that condition without evaluating later pairs. In Microsoft 365, IFS is useful when several outcomes need clear labels, rather than a deeply nested series of IF functions. It is available in Excel 2019 and later, including Microsoft 365.
For example, with a due date in A2, the opening formula classifies dates before today as overdue, today as due today, and dates after today as upcoming. TODAY updates when the worksheet recalculates, so the classification changes as the calendar advances. That is useful for a live task list, but it is not suitable when you need a fixed historical report unless you replace the moving reference with a fixed date.
Compare one date with another
To compare two cells, test the first date against the second. With a date in A2 and a comparison date in B2, use:
=IFS(A2<B2,"Earlier",A2=B2,"Same date",A2>B2,"Later")
The formula returns Earlier if A2 precedes B2, Same date if the stored values match, and Later if A2 follows B2. For example, if A2 contains 10 March 2026 and B2 contains 15 March 2026, the result is Earlier. The order of the three tests is easy to read, although the final comparison could also be used as a catch-all after the first two.
Dates should be entered as real Excel dates, not text that merely looks like a date. A typed value such as 3/10/2026 can be interpreted differently depending on regional date settings: it may mean March 10 or 3 October. For formulas that define a date boundary, DATE(year,month,day) is an unambiguous way to create a date value. It also avoids relying on how a particular computer interprets a slash-formatted date.
Classify dates into ranges
IFS is especially handy for assigning labels to date ranges. Suppose A2 contains an event date and you want to divide dates into before 2026, during 2026, and 2027 or later. Use:
=IFS(A2<DATE(2026,1,1),"Before 2026",A2<DATE(2027,1,1),"In 2026",A2>=DATE(2027,1,1),"2027 or later")
The first test catches every date before January 1, 2026. If that is false, the second test catches dates earlier than January 1, 2027, which means dates in 2026. The final test catches January 1, 2027, and later. A date of December 31, 2026 therefore returns In 2026, while January 1, 2027 returns 2027 or later.
The order matters because IFS stops as soon as it finds a true test. If a broad test such as A2<DATE(2027,1,1) came first, it would also be true for dates in 2025, so those would never reach the Before 2026 condition. When designing bands, write down each lower and upper boundary first and check that there are no unintended gaps or overlaps.
Build a rolling deadline status
For a list of deadlines, you can compare each date with TODAY and use date arithmetic to identify a time window. With the deadline in A2, this example distinguishes overdue items, those due within seven days, those due within 30 days, and later deadlines:
=IFS(A2<TODAY(),"Overdue",A2<=TODAY()+7,"Due within 7 days",A2<=TODAY()+30,"Due within 30 days",TRUE,"More than 30 days away")
TODAY()+7 means the date seven days after today; the comparison includes that boundary because it uses less than or equal to. Since the overdue test comes first, an overdue date cannot accidentally be labelled as within a future window. TRUE at the end acts as the final catch-all: if none of the earlier conditions matched, Excel returns More than 30 days away. This is also a useful way to ensure IFS has a result for every valid date.
For example, if today is May 1 and A2 is May 8, the formula returns Due within 7 days. If A2 is May 9, it returns Due within 30 days. Because TODAY changes, these labels move automatically over time. If your process counts business days rather than calendar days, this formula does not account for weekends or holidays; those rules require a different calculation, such as using WORKDAY or NETWORKDAYS to establish the relevant date boundary.
Handle blanks and dates that include times
A blank cell can create an unexpected classification. In comparisons, a blank may behave like zero, which can make it look earlier than a real date. Test for a missing value before testing the date itself. With a due date in A2, use:
=IFS(A2="","Missing date",A2<TODAY(),"Past",A2=TODAY(),"Today",A2>TODAY(),"Future")
The first condition catches an empty A2 and returns Missing date; only nonblank entries proceed to the date comparisons. If cells may contain spaces or imported text, a blank test alone may not be enough to validate the input. Check that the source column contains actual dates, and clean or correct text values before relying on date comparisons.
Excel date-and-time values include a fractional part for the time. As a result, two cells displayed as the same calendar date may not be equal: a value at 9:00 a.m. differs from one at 3:00 p.m., even if the cells are formatted to show only the date. To compare calendar days while ignoring time, use INT to remove the fractional time portion. With date-and-time values in A2 and B2, use:
=IFS(INT(A2)<INT(B2),"Earlier day",INT(A2)=INT(B2),"Same day",INT(A2)>INT(B2),"Later day")
This formula compares the whole-number date portions, so two times on the same date return Same day. It is appropriate only when the time of day is intentionally irrelevant. If the exact timestamp matters—for example, when checking whether a submission arrived before a deadline—compare the original values instead.
Choose IFS, IF, or AND
IFS is a good choice when each individual condition leads to a different result, such as a sequence of date bands. It is usually easier to maintain than nested IF formulas because the condition-result pairs appear in a flat list. However, IFS is not automatically the clearest option for every test.
- Use a simple IF when there are only two outcomes, such as on time or late.
- Use IFS when you have several ordered, mutually exclusive outcomes, such as overdue, due soon, and future.
- Use AND inside an IFS condition when a result requires a date to fall between two boundaries.
- Use OR inside an IFS condition when either of multiple date tests should produce the same result.
For example, sometimes you need to check that a date falls inside a specific inclusive interval rather than relying only on the order of upper limits. With the date in A2, use:
=IFS(AND(A2>=DATE(2026,4,1),A2<=DATE(2026,6,30)),"Second quarter",TRUE,"Outside second quarter")
The AND test is true only when A2 is on or after April 1 and on or before June 30, 2026. The TRUE condition supplies a result for dates outside that interval. This illustrates a general rule: IFS evaluates its conditions in sequence, but AND and OR let you combine several comparisons into one condition when a single outcome depends on them together.
Common errors and how to fix them
If Excel returns #N/A, none of the IFS conditions evaluated to TRUE. Add a final catch-all condition when a default outcome makes sense, and check that the earlier conditions cover the intended range. If Excel returns an error while entering the formula, check that every condition has a matching result and that the parentheses and quotation marks are balanced.
- A date looks right but compares incorrectly: It may be text rather than a numeric date. Re-enter or convert the value as a date, and verify its interpretation with the cell’s number format and source data.
- One date is classified in the wrong band: Review the order of the conditions. A broad earlier condition may capture a value before a later, more specific test can run.
- Dates on a boundary get the wrong label: Choose deliberately between < and <=, or between > and >=. For adjacent ranges, use consistent boundaries so each date belongs to exactly one range.
- Same-day values are reported as different: The cells may include different times. Compare INT of each value if only the calendar date matters.
- The formula works on one computer but not another: Regional settings can use semicolons rather than commas as formula argument separators. The examples here use commas; if Excel rejects them, use the separator required by that installation.
Also remember that displayed formatting does not change the stored value. Applying a date format to text does not necessarily convert it into a date, and hiding the time by formatting a timestamp as a date does not remove its time portion. When the result seems inconsistent with what is visible, inspect the source values rather than changing the IFS tests at random.
A practical setup for a date-status column
For a reusable deadline tracker, keep one date per row, add a status column, and use the same formula down the list. With deadlines in A2:A100 and the first status formula going in B2, enter the blank-safe rolling status formula in B2 and fill it down. In Microsoft 365 for Windows, drag the fill handle or double-click it to copy the formula alongside adjacent data; Excel adjusts the row reference as it fills. On Mac, the fill handle works similarly, though keyboard shortcuts and interface details can vary by keyboard settings.
Before sharing the tracker, decide whether overdue means strictly before today or includes the current day, and whether “within seven days” includes the seventh day. The examples above treat a date equal to today as due today, and include the date seven days ahead in the seven-day window. Clear definitions prevent users from interpreting labels differently. If you need a report that should not change tomorrow, use a fixed report date in a cell rather than TODAY, then compare each deadline with that reference cell.
Frequently Asked Questions
Can I use IFS to compare a date with today?
Yes. Use TODAY() as the moving reference date and test whether the cell is less than, equal to, or greater than today. Add a blank check first if empty cells are possible.
Why does my IFS date formula return #N/A?
IFS returns #N/A when no condition is true. Add a final TRUE condition with a default result, or revise the conditions so they cover every date you expect.
How do I compare dates but ignore the time?
Wrap both date-and-time values in INT within the comparisons. INT removes the fractional time component, allowing the formula to compare calendar days only.
Can I use a date written in quotation marks in an IFS formula?
A quoted date is text, and how Excel interprets date-like text can depend on regional settings. Use DATE(year,month,day) for a clear date boundary, or reference a cell containing a genuine Excel date.
Does IFS check every condition?
No. It checks conditions in order and returns the result paired with the first true condition. Put overlapping conditions in the intended priority order, and use a final catch-all only when it represents a sensible default.
Related
- Excel Table Names: Where to Find, View, and Change Them
- Excel Weekly Meal Planner: Build a Reusable Template
- Excel Web Keyboard Shortcuts: Essential Keys and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day Name Formula: Show the Weekday from a DateTo show the weekday name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- Excel Filter Table Without Hiding Rows: Best OptionsExcel’s built-in filters hide rows that do not match, so you cannot use a standard table filter to…
- How to Highlight in Excel Using a FormulaTo highlight cells or entire rows in Excel based on a condition, select the range, then choose Home…
- Calculate Age in Excel in Years with Simple FormulasTo calculate completed age in years in Excel, put each birth date in column A and, with the…
- Excel Formula Opposite of CONCATENATE: Split Text ApartThe closest Excel formula to the opposite of CONCATENATE is =TEXTSPLIT(A2,"|"): it takes text in A2 and splits…
- Excel MID Cell Reference: Extract Text from a CellTo extract characters from a cell in Excel, use the MID function with that cell as its first…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.