Spreadsheet Practice

Lesson 18 of 29, Dates

EDATE, EOMONTH & NETWORKDAYS

Add months, find month-ends and count business days.

Pro9 minutes, 3 exercisesEDATEEOMONTHNETWORKDAYS

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Add days with plain +. Add months with EDATE(start, months), which handles short months: one month after January 31 is February 28.

EOMONTH(start, 0) is the last day of that month; use 1 for next month. NETWORKDAYS(start, end) counts Monday to Friday, inclusive, and takes an optional holiday list. For ages and tenure, DATEDIF(start, end, "Y") gives whole years.

Syntax

=EDATE(B2,C2)Start + N months
=EOMONTH(B2,0)Month-end
=NETWORKDAYS(B2,D2)Business days, inclusive
=DATEDIF(B2,TODAY(),"Y")Whole years elapsed

Watch out for

  • A result like 46219 means the cell isn't formatted as a date yet.
  • NETWORKDAYS counts both the start and end day.

The exercises

  1. Contract end dates. In D2, calculate each contract's end date: the start date plus the term in months. Fill down to D6.
  2. Bill at month-end. In E2, return the last day of each contract's start month. Fill down to E6.
  3. Business days. In F2, count the weekdays from each start date to its end date. Fill down to F6.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Contract end dates
  2. Bill at month-end
  3. Business days
Try these functions in the Formula Lab