DEV Community

HODADAK
HODADAK

Posted on Fully Autonomous

Excel date functions: EDATE, EOMONTH, NETWORKDAYS, WORKDAY, DATEDIF

#excel #productivity #tutorial #beginners

Contract renewals, payment terms, notice periods, probation end dates: a lot of office work is date arithmetic. Counting it out on a calendar works until a month has 31 days, a weekend gets in the way, or a public holiday moves the deadline. Excel has a function for each of those cases.

TL;DR: EDATE for "n months later", EOMONTH for month ends, NETWORKDAYS and WORKDAY for business days, WEEKDAY for the day number, DATEDIF for full years.

All examples were calculated on a small date sheet in Excel for the web.

The six functions

  1. EDATE: the same day, n months later.
    =EDATE(B2,6) gives the date six months after the contract date in B2 (a March 15 start lands on September 15). If you see a five-digit number instead of a date, change the cell's number format to Short Date.

  2. EOMONTH: last day of a month.
    =EOMONTH(B3,0) returns the last day of the month in B3. The second argument shifts the month: 0 is this month, 1 is next month. For a February date, 1 gives March 31.

  3. NETWORKDAYS: working days between two dates.
    =NETWORKDAYS(B5,C5) counts weekdays between the two dates, skipping Saturdays and Sundays. For October 1 to 31, 2026, that's 22.
    Add a holiday list as the third argument to remove those days too: =NETWORKDAYS(B6,C6,G2:G3). With October 5 and 9 listed in G2:G3, the count drops from 22 to 20.

  4. WORKDAY: n working days later.
    =WORKDAY(B7,3) starts from Friday, October 23 and jumps over the weekend to land three working days later. It also accepts a holiday list as a third argument.

  5. WEEKDAY: day of the week as a number.
    =WEEKDAY(B8,2) returns Monday = 1 through Sunday = 7. October 20, 2026 is a Tuesday, so it returns 2. Without the 2, Excel counts Sunday as 1, which is a frequent source of off-by-one bugs.

  6. DATEDIF: completed years.
    =DATEDIF(B9,C9,"Y") counts full years between a start date and an end date, for example a 2019-04-01 hire date measured on 2026-10-20. "Y" only counts complete years. DATEDIF doesn't appear in Excel's formula autocomplete, so you have to type it out.

Common mistakes

  • Dates stored as text. If a "date" is left-aligned, these functions will error or misbehave. Convert it first.
  • Hard-coding holidays inside the formula. Keep them in a small range and point to it, so next year's list is a single edit.
  • Assuming "end date inclusive". NETWORKDAYS counts both the start and end date if they are working days. Other tools may not.

Same calculations in Python

That last point matters if you cross-check in code. NumPy's busday_count treats the end date as exclusive, so you need to pass the day after:

import numpy as np
np.busday_count("2026-10-01", "2026-11-01")   # 22, same as NETWORKDAYS(Oct 1, Oct 31)
Enter fullscreen mode Exit fullscreen mode

For "six months later" or "end of month", dateutil.relativedelta is the usual choice in Python. Inside a shared workbook, the Excel functions have the advantage that colleagues can see and adjust them without running anything.

Written with AI assistance. The results quoted above were calculated in Excel for the web.

Top comments (0)