Counting Days Between Dates

One of the most common date tasks is finding how much time separates two dates: days until a deadline, age in years, or the length of a project.

Because spreadsheets store dates as numbers, the simplest answer is plain subtraction. For more advanced needs like counting whole months or full years, the DATEDIF function steps in.

In this lesson you will master both the quick subtraction approach and the precise DATEDIF approach.

To count the days between an earlier date in A2 and a later date in B2, just subtract.

The result is the number of days between them. If A2 is June 1 and B2 is June 18, then =B2-A2 returns 17.

Put the later date first so the result is positive. Subtracting the other way gives a negative number, which simply means the order is reversed.

A common surprise: after subtracting dates, the answer cell sometimes shows a date instead of a number, like 1/17/1900.

That happens because the cell inherited a date format. The math is correct; only the display is wrong. Fix it by changing the cell's format to Number with zero decimals.

Once formatted as a number, you will see the clean day count you expected.

Plain subtraction counts the gaps between days, not the days themselves. From June 1 to June 18, subtraction gives 17, but there are actually 18 calendar days if you count both ends.

If your situation needs both the start and end day included, add 1 to the result.

So =B2-A2+1 gives the inclusive count. Decide which one your report needs.

For counting whole months or years, subtraction is awkward. The DATEDIF function is built for it. It takes three arguments: start date, end date, and a unit code in quotes.

The unit code controls what it counts:

• "D" counts days
• "M" counts complete months
• "Y" counts complete years

Note: DATEDIF is a hidden legacy function; it works but will not show autocomplete in Excel.

Use the "M" unit to count complete months between two dates. Partial months are ignored.

If A2 is January 15 and B2 is April 10, =DATEDIF(A2, B2, "M") returns 2, because only two full months have elapsed (the third month is not yet complete on the 10th).

This is exactly how subscription tenure or contract length is usually measured.

The "Y" unit gives complete years, which is the correct way to compute age.

If A2 holds a birth date, then comparing it to today gives the person's age in full years. It correctly returns the lower number until the birthday actually passes in the current year.

This avoids the rounding mistakes you get from dividing day differences by 365.

DATEDIF has extra unit codes for breaking a span into parts:

• "YM" = months remaining after counting whole years
• "MD" = days remaining after counting whole months
• "YD" = days remaining after counting whole years

These let you phrase a duration like 2 years, 3 months, 5 days by combining three DATEDIF calls.

Plain subtraction counts every calendar day, including weekends. To count only business days, use NETWORKDAYS.

It counts weekdays between two dates and lets you supply a list of holidays to skip. So =NETWORKDAYS(A2, B2) returns Monday-to-Friday days, and an optional third argument points to a holiday range.

This is the right tool for delivery estimates and SLA timelines.

Suppose an invoice due date sits in A2 and you want to flag how many days it is overdue compared to today, but show 0 if it is not overdue yet.

Subtract the due date from today, then wrap it in MAX with 0 so a not-yet-due invoice never shows a negative number.

This single formula gives a clean, sensible overdue count for a collections report.

Once you have a day count, you can convert it into other units. To express a span in whole weeks, divide the day difference by 7 and round down with INT.

So =INT((B2-A2)/7) tells you how many complete weeks fit between the two dates. A 17-day gap becomes 2 weeks.

The same idea works for any unit: divide the day count and round however your report needs.

Test your date-difference knowledge.

You learned several ways to measure time spans:

• =B2-A2 gives the day gap (put the later date first); add 1 for an inclusive count.
• Reformat the result cell to Number if it shows a date.
• DATEDIF(start, end, "D"/"M"/"Y") counts whole days, months, or years; great for age and tenure.
• NETWORKDAYS counts business days and can skip holidays.
• Wrap with MAX(0, ...) to avoid negative overdue counts.

You have completed the Date and Time Functions course.