Excel Formula for Checking Dates: Validity and Ranges

Formulas & functions · Updated November 19, 2026

If the date is in A2, use =ISNUMBER(A2) to check whether Excel stores it as a numeric value; for an actual date such as 15-Mar-2026, the formula returns TRUE. To check whether a date falls between two limits, compare it with the start and end dates instead. The right formula depends on whether you mean a real Excel date, a date within a range, a date that has passed, or text that merely looks like a date.

What it means to check a date in Excel

Excel represents dates as serial numbers, with each whole number corresponding to a day. The date format controls how that number appears in a cell; it does not, by itself, turn text into a date. This explains why a cell can display something date-like but still fail a comparison, or why a numeric value can pass a basic date check even though it was not intended as a date.

First decide what you need to verify. Common checks include:

  • Whether a cell contains a numeric value Excel can use as a date.
  • Whether the date is inside an allowed period.
  • Whether it is today, in the past, or in the future.
  • Whether a text entry can be converted into a date.
  • Whether a date falls in a particular month, year, or weekday.

These are different questions, so one formula is not suitable for every situation. For example, ISNUMBER can identify a numeric value, but it cannot determine whether that value is a sensible date for your records. Add boundaries when your dates must fall within a known period.

Check whether a cell contains a usable date value

With date entries in A2:A100, enter this formula in B2 and copy it down to check each corresponding cell. If A2 contains a stored Excel date, the result is TRUE; if it contains ordinary text such as a date typed with a leading apostrophe, the result is FALSE.

=ISNUMBER(A2)

ISNUMBER is a useful first test because Excel dates are numbers. However, a number such as 50000 also returns TRUE, even if it is an invoice ID rather than a date. To reduce false positives, compare the value with acceptable date limits. With the candidate date in A2, the earliest allowed date in B1, and the latest in C1, this returns TRUE only when A2 is numeric and within the inclusive limits. For example, if B1 is 1-Jan-2026, C1 is 31-Dec-2026, and A2 is 15-Mar-2026, the result is TRUE.

=AND(ISNUMBER(A2),A2>=$B$1,A2<=$C$1)

The dollar signs keep the boundary references fixed if you copy the formula down. Make sure B1 and C1 are real Excel dates too; text boundaries can make comparisons unreliable. The formula treats both endpoints as allowed, so a date exactly equal to either boundary passes.

Convert text that looks like a date

If A2 contains text rather than a stored date, DATEVALUE can convert recognizable date text into an Excel date serial. With the text entry in A2, this formula returns the converted value when Excel recognizes it; if A2 contains 15-Mar-2026 and the computer’s regional settings recognize that format, the result represents 15 March 2026. Format the result cell as a date to display it as a calendar date rather than a serial number.

=IFERROR(DATEVALUE(A2),"Invalid date text")

DATEVALUE’s interpretation can depend on regional date settings. For instance, a string such as 04/05/2026 may mean April 5 or May 4, depending on the expected month-day order. An unambiguous entry such as 15-Mar-2026 is easier for a reader to interpret, but month names and accepted formats can still depend on language and settings. If you import dates from another system, verify the converted results rather than assuming every text value was read as intended.

When a column is mixed—some cells are genuine dates and others are text—consider converting the source data into a consistent format before analysis. A formula that checks a text cell with a date comparison may return an error or produce an unintended result. DATEVALUE is a conversion step, not proof that the original text meant the date you intended.

Test whether a date is inside a period

A range check is useful for reporting periods, booking windows, warranty dates, and similar rules. With the candidate date in A2, the start date in B1, and the end date in C1, use the same inclusive comparison shown above. If the dates are 1-Jan-2026 and 31-Dec-2026 and A2 is 15-Mar-2026, the result is TRUE. A date on 31-Dec-2026 also returns TRUE because the formula includes the endpoints.

If your rule excludes the final day, change the upper comparison from less than or equal to to strictly less than. With the same A2, B1, and C1 layout, this version returns FALSE when A2 equals C1; for 15-Mar-2026 inside the example 2026 period, it returns TRUE.

=AND(ISNUMBER(A2),A2>=$B$1,A2<$C$1)

Use a clear convention for range boundaries. A period described as “through December 31” is usually inclusive, while a system that defines an end instant at the start of December 31 may require a different rule. If cells include times as well as dates, a date that appears to be the final day may actually contain a time later than midnight. Decide whether the comparison should include the entire final day before choosing the operators.

Check dates against today

TODAY returns the current date according to the computer’s date settings and updates when Excel recalculates. With a date in A2, this formula returns TRUE if the value is earlier than today. Assuming TODAY evaluates to 3-Nov-2026, an A2 value of 1-Nov-2026 returns TRUE, while 5-Nov-2026 returns FALSE.

=AND(ISNUMBER(A2),A2<TODAY())

The ISNUMBER test matters because an empty cell can otherwise behave like zero in a comparison and be mistaken for an old date. To flag a future date instead, reverse the comparison. With the date in A2 and TODAY evaluating to 3-Nov-2026, an A2 value of 5-Nov-2026 returns TRUE.

=AND(ISNUMBER(A2),A2>TODAY())

To test whether A2 is today, compare it with TODAY. If A2 contains a date with a time component, however, direct equality can return FALSE even when the calendar day is today: a value at noon is not equal to today at midnight. With a date or timestamp in A2, this formula ignores the time portion; assuming TODAY is 3-Nov-2026, a value of 3-Nov-2026 12:00 PM returns TRUE.

=AND(ISNUMBER(A2),INT(A2)=TODAY())

These checks recalculate, so a row marked as upcoming today may be overdue tomorrow without anyone editing it. That is useful for live trackers, but it can be surprising in a report that should preserve the status as of a past date. For a fixed reporting date, store that date in a cell and compare against the cell instead of using TODAY.

Check months, years, and weekdays

Sometimes a date is valid but must match a particular month or year. With a date in A2, this formula returns TRUE for any date in November 2026 and FALSE for other months or years. If A2 is 15-Nov-2026, it returns TRUE. The outer IF prevents YEAR and MONTH from being applied to ordinary text.

=IF(ISNUMBER(A2),AND(YEAR(A2)=2026,MONTH(A2)=11),FALSE)

To identify weekdays, WEEKDAY can return a number for each day. With a valid date in A2, =WEEKDAY(A2,2) numbers Monday as 1 through Sunday as 7; for 15-Mar-2026, a Sunday, it returns 7. The second argument matters: using 2 gives the Monday-to-Sunday sequence, which makes weekend checks straightforward.

With a date in A2, this formula returns TRUE for Saturday or Sunday and FALSE for a weekday. For 15-Mar-2026, it returns TRUE. The ISNUMBER check prevents a blank from being treated as a date for this purpose.

=AND(ISNUMBER(A2),WEEKDAY(A2,2)>5)

Common errors and ways to avoid them

  • A date displays but does not compare correctly: it may be text. Check it with ISNUMBER, then convert or clean the source data if needed.
  • A number passes the date test: ISNUMBER accepts all numbers. Add realistic lower and upper bounds that match the purpose of the column.
  • A date appears one day off: check whether the cell includes a time, whether a text date was interpreted using the expected regional order, and whether the source system uses a different date convention.
  • A same-day comparison fails: the cell may contain a time. Compare INT of the cell with TODAY when the time should be ignored.
  • A formula shows a serial number: the calculation may be correct; apply a date format to the result cell using Home > Number Format.

Excel for Microsoft 365 on Windows uses the same formulas described here in desktop workbooks. On Mac and Excel for the web, the formulas work the same way, although available menus and their layout can vary slightly. Date display and text interpretation may also reflect the computer or workbook’s regional settings, so shared files deserve a quick check on the systems where they will be used.

Prevent bad entries with Data Validation

A formula can identify a problem after data has been entered, while Data Validation can restrict what users enter in the first place. Select the cells, then choose Data > Data Validation. In the settings, choose Date under Allow and set a condition such as between, with start and end dates. The precise dialog wording can differ slightly across Excel platforms, but the Date validation option is available in desktop Excel.

Validation is helpful for a form or shared tracker, but it is not a substitute for checking imported data. Pasting or loading values from other sources can create inconsistencies, and a date rule does not explain whether a rejected entry was mistyped or whether the allowed period needs to change. A practical workbook may use both: validation to guide new entries and a status formula to review existing rows.

Frequently Asked Questions

What is the simplest Excel formula to check a date?
With the candidate value in A2, =ISNUMBER(A2) checks whether Excel stores it as a number. A TRUE result is a useful first check, but numeric values are not automatically valid or meaningful dates; use reasonable date boundaries for stronger validation.

How do I check whether a date is between two dates?
Put the candidate in A2, the start date in B1, and the end date in C1. Use =AND(ISNUMBER(A2),A2>=$B$1,A2<=$C$1). It returns TRUE for dates on either boundary as well as dates between them.

How do I check if a date has passed?
With a date in A2, use =AND(ISNUMBER(A2),A2<TODAY()). It returns TRUE for dates before today and excludes blanks. TODAY changes as Excel recalculates, so it is not a fixed historical reference.

Why does Excel treat my date as text?
The entry may have come from an import, may include a leading apostrophe, or may use a date order that Excel does not recognize under the current regional settings. Check it with ISNUMBER and, when appropriate, convert recognizable text with DATEVALUE; confirm the result before relying on it.

How can I check a date while ignoring its time?
With a date and possible time in A2, use =AND(ISNUMBER(A2),INT(A2)=TODAY()) to test whether its calendar day is today. INT removes the fractional time portion from a valid date-time serial.

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.