Date Formulas for Deadlines and Workdays

Most spreadsheet work with dates comes down to three questions: how much time is between two dates, how many working days fall in that range, and what date is some number of months from now. Excel has a specific function for each, and Microsoft's own documentation gives worked examples for all three.

Counting days, months, or years between two dates: DATEDIF

Microsoft's documentation describes DATEDIF as calculating the number of days, months, or years between two dates, and notes it is useful in formulas where you need to calculate an age. The syntax is DATEDIF(start_date, end_date, unit), where unit is one of "Y" (complete years), "M" (complete months), "D" (days), "YD" (days, ignoring the years), "YM" (months, ignoring days and years), or "MD" (days, ignoring months and years).

Microsoft's own documented example: from June 1, 2001 to August 15, 2002, =DATEDIF(start_date, end_date, "D") returns 440 days, and =DATEDIF(start_date, end_date, "YD") returns 75 days, the gap between June 1 and August 15 with the year ignored.

from datetime import date

start = date(2001, 6, 1)
end = date(2002, 8, 15)

total_days = (end - start).days

# YD treats the years as equal and only looks at month/day
same_year_start = date(end.year, start.month, start.day)
yd_result = (end - same_year_start).days

print("Total days:", total_days)
print("YD (ignoring year):", yd_result)

Running this prints 440 total days and 75 for the year-ignored gap, matching Microsoft's documented result exactly.

One warning worth repeating from Microsoft's own page: DATEDIF exists mainly to support older workbooks from Lotus 1-2-3, and the "MD" unit specifically can produce negative numbers, zeros, or inaccurate results. Microsoft's documentation does not recommend using "MD" for this reason, and suggests a DATE-function-based workaround if you need the remaining days after the last completed month.

Counting only working days: NETWORKDAYS

For deadlines that should skip weekends, and optionally holidays, Microsoft's documentation describes NETWORKDAYS as returning the number of whole working days between a start and end date, excluding weekends and any dates listed as holidays. The syntax is NETWORKDAYS(start_date, end_date, [holidays]), with holidays as an optional argument.

Microsoft's documented example uses a project running from October 1, 2012 to March 1, 2013, with three specific holidays (November 22, 2012; December 4, 2012; and January 21, 2013):

• =NETWORKDAYS(A2,A3) with no holidays listed returns 110
• Adding the single November 22 holiday returns 109
• Adding all three holidays returns 107

Checking this with a plain Python loop that counts Monday through Friday, excluding the listed holiday dates:

from datetime import date, timedelta

def networkdays(start, end, holidays=()):
    holidays = set(holidays)
    current = start
    workdays = 0
    while current <= end:
        if current.weekday() < 5 and current not in holidays:
            workdays += 1
        current += timedelta(days=1)
    return workdays

start = date(2012, 10, 1)
end = date(2013, 3, 1)

print("No holidays:", networkdays(start, end))
print("One holiday:", networkdays(start, end, [date(2012, 11, 22)]))
print("Three holidays:", networkdays(start, end, [date(2012, 11, 22), date(2012, 12, 4), date(2013, 1, 21)]))

This prints 110, 109, and 107, matching Microsoft's documented results in all three cases. If your team works a different schedule, such as a six-day week or Sunday-Monday as the weekend, Microsoft's documentation points to NETWORKDAYS.INTL, which adds a parameter for which days count as weekend days.

Finding a date some months out: EDATE

When you need a due date or renewal date that falls a fixed number of months from a known date, Microsoft's documentation describes EDATE as returning the serial number representing the date that many months before or after a given start date, and notes it is specifically meant for maturity dates or due dates that fall on the same day of the month as the issue date. The syntax is EDATE(start_date, months), where a positive number of months gives a future date and a negative number gives a past date.

Microsoft's documented example starts from January 15, 2011:

• =EDATE(A2,1) returns February 15, 2011
• =EDATE(A2,-1) returns December 15, 2010
• =EDATE(A2,2) returns March 15, 2011

from datetime import date
import calendar

def edate(start, months):
    month = start.month - 1 + months
    year = start.year + month // 12
    month = month % 12 + 1
    day = min(start.day, calendar.monthrange(year, month)[1])
    return date(year, month, day)

start = date(2011, 1, 15)
print(edate(start, 1))
print(edate(start, -1))
print(edate(start, 2))

This prints 2011-02-15, 2010-12-15, and 2011-03-15, matching Microsoft's documented results.

Putting the three together

A practical pattern for tracking deadlines in a shared sheet:

• Use EDATE to generate a renewal or review date a fixed number of months from a start date, for example a contract that renews every 12 months.
• Use NETWORKDAYS to show how many working days remain before a deadline, which is usually more useful for staffing or planning than the raw calendar day count.
• Use DATEDIF with the "Y" or "M" unit when you need a clean age or tenure figure, such as years of service, and avoid the "MD" unit given Microsoft's own warning about it.

Key takeaways

• DATEDIF calculates days, months, or years between two dates but should not be used with the "MD" unit, per Microsoft's own documented warning.
• NETWORKDAYS counts only weekdays between two dates and can exclude a list of specific holiday dates.
• EDATE returns a date a fixed number of months before or after a start date, useful for due dates and renewals.
• Every example in this article reproduces Microsoft's documented results exactly when checked with an independent Python calculation.
• For non-standard weekends, Microsoft's documentation points to NETWORKDAYS.INTL instead of NETWORKDAYS.