Excel: Check if Two Dates Differ by More or Less Than a Number

Updated 6 November 2026 · Standard formula, shown below

This article explains how to compare two dates in Excel and determine whether the difference between them is greater than or less than a specific number of days. You will learn the core formulas, how to handle different units, and common pitfalls to avoid.

Understanding Date Differences in Excel

Excel stores dates as serial numbers, where each whole number represents a day. January 1, 1900, is serial number 1, and January 2, 1900, is 2, and so on. This means that subtracting one date from another gives you the number of days between them. For example, if cell A1 contains 1/1/2024 and B1 contains 1/10/2024, the formula =B1-A1 returns 9. This simple subtraction is the foundation for all date-difference comparisons.

The Basic Formula: Comparing to a Number

To check if the difference between two dates is more than a number (say, 30 days), you use a logical test. The formula =IF(B1-A1>30, "More than 30", "Not more than 30") returns a custom message. To output TRUE/FALSE instead, use =B1-A1>30. For checking if the difference is less than a number, replace > with <. You can also use >= (greater than or equal) or <= (less than or equal).

Using the DATEDIF Function for Different Units

If you need to compare differences in months or years rather than days, use the DATEDIF function. Its syntax is DATEDIF(start_date, end_date, unit). The unit can be “d” for days, “m” for complete months, or “y” for complete years. For example, =DATEDIF(A1, B1, "m") returns the number of full months between the two dates. To check if that is more than 6, use =IF(DATEDIF(A1,B1,"m")>6, "Yes", "No"). Note that DATEDIF counts complete months, so it ignores partial months.

Handling Absolute Differences (Either Direction)

Sometimes you only care about the magnitude of the difference, regardless of which date is later. Use the ABS function to get the absolute value. The formula =ABS(B1-A1)>30 returns TRUE if the dates are more than 30 days apart in either direction. This is useful for checking if a task was completed within a window, regardless of whether it was early or late.

Common Mistakes and How to Avoid Them

Text-formatted dates: If your dates are stored as text, subtraction won’t work. Convert them to real dates using DATEVALUE or the Text to Columns feature.
Wrong order: If the start date is after the end date, the difference is negative. Use ABS to avoid negative results in comparisons.
Time components: If your dates include times (e.g., 1/1/2024 6:00 PM), the difference will be a decimal. For example, 1/1/2024 12:00 PM minus 1/1/2024 8:00 AM is 0.1667 days. To compare only whole days, use INT to round down: =INT(B1)-INT(A1).
Using DATEDIF with wrong order: DATEDIF returns an error if the start date is later than the end date. Swap the arguments or use IF to handle both orders.

Comparing Dates with Conditional Formatting

You can visually highlight rows where the date difference exceeds a threshold. Select the range, go to Home > Conditional Formatting > New Rule > Use a formula. Enter a formula like =$B1-$A1>30 (adjust for your columns), and set a format. This applies the highlight to the entire row based on the dates in that row. This is a powerful way to spot overdue items or upcoming deadlines at a glance.

Frequently Asked Questions

How do I check if two dates are within 7 days of each other?

Use =ABS(B1-A1)<=7. This returns TRUE if the absolute difference is 7 days or less. For a strict “less than 7 days�, use <7 instead.

What if my dates are in different cells or different sheets?

Simply reference the cells normally, even across sheets. For example, =Sheet2!B1-Sheet1!A1 works. Just ensure both cells contain real dates, not text.

Can I compare dates in the same cell?

No, you need two separate date values. If you have a date range in one cell like "1/1/2024 - 1/10/2024", you'll need to split it using Text to Columns or the LEFT/RIGHT functions before subtracting.

Why does DATEDIF return a #NUM! error?

This occurs when the start date is later than the end date. DATEDIF requires the start date to be earlier. Swap the arguments or use IF to handle the order.

How do I count the number of days between two dates excluding weekends?

Use the NETWORKDAYS function: =NETWORKDAYS(A1, B1). It returns the number of working days (Monday-Friday) between the two dates. To include holidays, add a third argument with a range of holiday dates.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.