To check whether a date falls between two dates in Excel, put an AND test inside IFS: IFS(AND(date>=start,date<=end),result,TRUE,other_result). The two comparisons make the range inclusive, so dates equal to either boundary count as inside it; the examples below show how to adapt the formula for real worksheets.
The basic IFS formula for a date range
IFS tests conditions in order and returns the result associated with the first condition that is TRUE. To test a date against both a start date and an end date, combine the comparisons with AND. A date cannot be within a range unless it meets both limits.
With the date to check in A2, use this formula to identify dates from January 1 through March 31, 2025:
=IFS(AND(A2>=DATE(2025,1,1),A2<=DATE(2025,3,31)),"In range",TRUE,"Outside range")
If A2 contains March 31, 2025, the result is “In range.” If it contains April 1, 2025, the result is “Outside range.” The final TRUE acts as a catch-all condition, so the formula returns “Outside range” whenever the date does not satisfy the range test. Without a catch-all, IFS returns #N/A if none of its conditions is TRUE.
The DATE(year,month,day) function builds actual Excel dates from numbers. This is useful in formulas because a date such as January 1 can otherwise be interpreted differently depending on regional date settings. You can replace the text results with a number, a calculation, or a cell reference if that better fits your worksheet.
How IFS and AND work together
The general pattern is IFS(logical_test1,value_if_true1,logical_test2,value_if_true2,...). Each logical test must evaluate to TRUE or FALSE, and each test has a matching result. For one date interval, AND supplies a single logical test by checking that the date is not earlier than the start and not later than the end.
date>=startincludes the start date.date<=endincludes the end date.AND(first_test,second_test)is TRUE only when both comparisons are TRUE.TRUE,default_resultprovides a result for dates that failed earlier tests.
Use > and < instead of >= and <= when the boundary dates should be excluded. For example, a date strictly after a start and strictly before an end uses AND(A2>start,A2<end). Be deliberate about this choice: changing one comparison changes what happens on the first or last day.
Use cells for the start and end dates
For a reusable worksheet, store the date being checked in A2, the start date in B2, and the end date in C2. Enter real Excel dates in B2 and C2, then use this formula:
=IFS(AND(A2>=B2,A2<=C2),"In range",TRUE,"Outside range")
If A2 is June 15, B2 is June 1, and C2 is June 30 of the same year, the result is “In range.” If A2 is May 31, the result is “Outside range.” Referencing cells makes it easy to adjust the period without editing the formula. Copy the formula down to check additional dates; Excel will adjust the relative references for each row.
For a list that uses the same period in every row, lock the start and end references so they do not move when you fill the formula down. With dates to check in A2:A100 and the shared start and end in B2 and C2, respectively, use:
=IFS(AND(A2>=$B$2,A2<=$C$2),"In range",TRUE,"Outside range")
The dollar signs make B2 and C2 absolute references, while A2 remains relative. When copied to the next row, Excel changes A2 to A3 but leaves the boundaries as B2 and C2. In Excel for Microsoft 365 on Windows, press F4 while editing a cell reference to cycle through reference types; on a Mac, the equivalent shortcut is usually Command+T, though keyboard settings can affect it.
Classify dates into several ranges
You can put several date-range tests in one IFS formula. The formula below assigns a quarter label to dates in the first half of 2025 and labels all other dates as outside those ranges:
=IFS(AND(A2>=DATE(2025,1,1),A2<=DATE(2025,3,31)),"Q1",AND(A2>=DATE(2025,4,1),A2<=DATE(2025,6,30)),"Q2",TRUE,"Outside")
If A2 is February 10, 2025, the result is “Q1”; if it is May 20, 2025, the result is “Q2.” A date in July returns “Outside.” The order matters: IFS returns the first matching result and does not continue checking later tests. Here the ranges do not overlap, so either quarter test can be evaluated first without changing the result.
If ranges overlap, put the condition that should take priority first, or revise the boundaries so there is no overlap. If there are gaps between periods, a date in a gap falls through to the final result. Check that each period’s start and end match the policy you intend to apply; for adjacent inclusive periods, the same boundary day could otherwise qualify for both.
Dates that include times
Excel stores dates as serial numbers and times as fractions of a day. A cell that displays June 30 might contain June 30 at 3:00 p.m. If the end-date cell C2 contains June 30 at midnight, the test A2<=C2 excludes that afternoon timestamp, even though the cell may display only the date.
When the end boundary is intended to include the whole calendar day, use a less-than test against the day after the end date. With the date or timestamp in A2 and date-only boundaries in B2 and C2, use:
=IFS(AND(A2>=B2,A2<C2+1),"In range",TRUE,"Outside range")
If A2 contains June 30 at 3:00 p.m., B2 is June 1, and C2 is June 30, the result is “In range.” This pattern includes every time on the end date but excludes midnight at the start of the following day. If both bounds are intentionally exact timestamps rather than whole calendar days, keep the inclusive comparisons and enter the intended times in the boundary cells.
Handle blank cells and invalid boundaries
A blank date cell should usually remain blank rather than being classified. Excel can treat a blank in a comparison like a zero, which may produce an unexpected category. With the date in A2 and the start and end dates in B2 and C2, this formula returns an empty string for a blank A2:
=IF(A2="","",IFS(AND(A2>=B2,A2<=C2),"In range",TRUE,"Outside range"))
With A2 empty, the result appears blank. With A2 equal to June 15 and B2 and C2 set to June 1 and June 30, the result is “In range.” The outer IF handles missing input before IFS evaluates the range. If blank start or end cells are possible, decide whether to show a warning, leave the result blank, or flag the row for correction rather than silently treating an incomplete range as valid.
Also check that the start date is not later than the end date. A reversed range will not match ordinary dates. You can add a separate validation check, or correct the input cells before relying on the classification. Data Validation can help limit what people enter, but it does not replace checking existing records or pasted values.
Common date-format problems
Excel dates are numeric values displayed using a date format. A value that looks like a date may instead be text, particularly if it was imported from another system. Comparisons between text and real dates can give surprising results or fail to behave as expected. Formatting a text value as a date does not necessarily convert it into an Excel date.
To investigate a suspicious cell, change its number format temporarily to General. A genuine date usually displays as a number; a text date remains text. You can also try entering the date directly into a blank cell and selecting a date format from Home > Number Format. If the source data is text, convert it using an appropriate import or conversion method before using the range formula. The right method depends on how the text is structured and on the workbook’s regional settings.
Other useful checks include confirming that the year is correct, making sure the start and end cells contain dates rather than labels, and checking for hidden time values. If Excel shows #NAME?, inspect the function names and quotation marks; if it shows #N/A, confirm that the IFS formula includes a default condition. Excel’s formula argument separator can vary with regional settings; if the installation expects semicolons, the commas shown in these examples may need to be changed.
When to use IF or a lookup instead
IFS is useful when each range needs a clear, separate condition and result, especially when you have several classifications. It is available in Excel 2019 and later, including Microsoft 365. For a single in-range/out-of-range decision, a regular IF can be shorter; it still needs AND to test both limits.
With the date in A2 and the boundaries in B2 and C2, this IF formula returns “In range” for June 15 when the limits are June 1 and June 30, and “Outside range” for May 31:
=IF(AND(A2>=B2,A2<=C2),"In range","Outside range")
For many changing date bands, a separate lookup table may be easier to maintain than a very long IFS formula. The best approach depends on how the ranges are organized and whether the boundaries are continuous, non-overlapping bands. IFS is straightforward for a modest number of explicit rules; a table can make a large set of rules easier for other people to review and update. Whichever approach you choose, document whether the endpoints are inclusive and what should happen to dates outside the listed periods.
Frequently Asked Questions
Can I use IFS to check whether a date is between two dates?
Yes. Put AND(date>=start,date<=end) in an IFS logical test. Use a final TRUE condition if you want to specify what the formula returns when the date is outside the range.
Does Excel IFS include the start and end dates?
That depends on the comparison operators. The common pattern using >= for the start and <= for the end includes both boundaries. Use strict > and < operators to exclude them.
Why does my end date not match when the cell has a time?
The cell may contain a time later than midnight, while the end-date value represents midnight at the start of that day. To include the entire end date when the boundary is date-only, compare the timestamp with < end_date+1.
What happens if no IFS condition is TRUE?
Excel returns #N/A if none of the supplied conditions matches. Add a final pair such as TRUE,"Outside range" to provide a default result.
Can I use IFS in older Excel versions?
IFS is available in Excel 2019 and newer, including Microsoft 365. In versions without IFS, use nested IF functions with the same AND date-range test, or open the workbook in a supported version.
Related
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Large for No Reason? Causes and Fixes to Try
- Excel Essential Keyboard Shortcuts for Everyday Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- Excel Find and Replace: Values, Formulas, and OptionsTo find and replace values in Excel, press Ctrl+H on Windows, enter the text or value under Find…
- Excel Timesheet Hours Formula: Calculate Time WorkedTo calculate a shift in decimal hours in Excel, put the start time in B2 and end time…
- Excel ISBLANK Function Example: Formulas and TipsIn Excel, use =ISBLANK(A2) to test whether cell A2 is genuinely empty; it returns TRUE if the cell…
- Excel INDEX MATCH With Table Structured ReferencesYes—Excel INDEX MATCH works with table structured references. For example, if a table named Inventory has columns Item…
- Excel LAMBDA Formula Examples for Reusable FunctionsExcel LAMBDA lets you turn a calculation into a reusable function, so you can name it once and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.