Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 93 ยท Text, Date and Time

Date Calculations: DATEDIF, EDATE, EOMONTH, NETWORKDAYS

Calculate with dates in Excel: differences with DATEDIF, adding months with EDATE and EOMONTH, workdays with NETWORKDAYS and WORKDAY.INTL, age and fiscal periods.

Dates are numbers in Excel, so you can subtract them, add to them and compare them. That is the easy part. The hard part is what people actually ask: "How old is this customer?", "When does the 90-day trial end?", "How many working days until the deadline, not counting holidays?", "Which fiscal quarter is this invoice in?" Each of these has a function that gets it right, and a naive formula that is wrong a few times a year.

This lesson builds on Dates and Times in Excel, where you saw that a date is a serial number. Here you will use the date functions that are not obvious: DATEDIF, EDATE, EOMONTH, NETWORKDAYS, WORKDAY and their .INTL versions. Excel cannot run on this site, so the TryIts use a small JavaScript model of these functions and a pandas version. They are stand-ins that follow the documented behavior, not Excel's own code.

Five date questions

How long? DATEDIF, or plain subtraction.
When after N months? EDATE and EOMONTH.
How many workdays? NETWORKDAYS and WORKDAY.
Age or tenure? DATEDIF with Y, M and YM.
1 / 2

Differences: subtraction and DATEDIF

The number of days between two dates is plain subtraction: =B2-A2. Format the result as General or Number, because Excel may format it as a date. For whole units, use DATEDIF(start, end, unit):

Unit Returns
"Y" Complete years
"M" Complete months
"D" Days (same as subtraction)
"YM" Months left over after whole years
"YD" Days left over after whole years
"MD" Days left over after whole months

DATEDIF is a legacy function. It works in every version but it does not appear in the formula suggestions and has no help page in the function wizard, so you must type it yourself. It returns #NUM! if the start date is after the end date. The "MD" unit is known to return odd results in some cases, so avoid it: compute leftover days with end - EDATE(start, months) instead.

A "complete" month means the end day-of-month has reached the start day-of-month. From January 31 to February 29 (in a leap year) there is 0 complete months, because the 29th has not reached the 31st.

Age and tenure

Age is the classic use: =DATEDIF(B2, TODAY(), "Y"). Do not use =(TODAY()-B2)/365: leap years make it wrong around birthdays. For a friendly tenure text, combine the units:

TEXT
=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"

Adding months: EDATE and EOMONTH

Adding 30 to a date moves it 30 days, and months are not 30 days long. EDATE(start, months) moves by calendar months and keeps the day-of-month. If the target month is too short, it clamps to the last day: EDATE(DATE(2025,1,31), 1) is February 28, 2025. Negative months go back in time.

EOMONTH(start, months) returns the last day of the month that is months away. With 0 it gives the end of the current month. It is the safest way to build month-end dates, and it works well for due dates such as "end of next month".

A trick: the first day of next month is =EOMONTH(A2, 0) + 1, and the first day of the current month is =EOMONTH(A2, -1) + 1. Both results are serial numbers, so apply a date format.

Workdays: NETWORKDAYS and WORKDAY

NETWORKDAYS(start, end, [holidays]) counts the working days between two dates, including both ends, skipping Saturdays and Sundays and every date in the optional holiday range. WORKDAY(start, days, [holidays]) goes the other way: it returns the date that is days working days from the start. The start date itself is not counted, so WORKDAY(Friday, 1) is Monday.

Keep holidays in their own range or named range, such as Holidays, and pass that name to every formula. When the list changes, every workday calculation updates.

Other weekends: .INTL

Not every country rests on Saturday and Sunday. NETWORKDAYS.INTL(start, end, [weekend], [holidays]) and WORKDAY.INTL(start, days, [weekend], [holidays]) take a weekend argument that is either a code or a seven-character text of 0 and 1, starting on Monday. A 1 marks a non-working day. So "0000011" is the usual Saturday-Sunday weekend and "0000001" makes only Sunday a day off. Codes such as 7 (Friday and Saturday) and 11 (Sunday only) are also accepted.

Run it: a date engine in JavaScript

The model below stores dates as serial numbers, exactly like Excel (a whole number per day, counted from 1900), and implements the functions described above. The dates are fixed, so the output is the same every time. In a real sheet, TODAY() would replace the constant "today".

JS DATEDIF, EDATE, EOMONTH and age

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that Ben, born one day later than Ana in June, is still 34 on June 15, while Ana has just turned 35. Notice too that EDATE from January 31 never produces an impossible date such as February 31: it clamps.

Fiscal periods

Many companies start their year in a month other than January. With the fiscal year starting in month S (a cell named FYStart), these patterns work:

  • Fiscal quarter: =INT(MOD(MONTH(A2) - FYStart, 12) / 3) + 1
  • Fiscal year (named after the year it ends in, when S is not 1): =YEAR(A2) + (MONTH(A2) >= FYStart)
  • Fiscal year end date: =EOMONTH(DATE(YEAR(A2) + (MONTH(A2) >= FYStart), FYStart, 1), -1)

MOD keeps the month count between 0 and 11 even when the month is smaller than the start. The fiscal year formula adds TRUE (which counts as 1) to the calendar year when you are already past the start month. If your company names its fiscal year after the year it starts, drop the + (MONTH...) part and adjust the label.

Run it: workdays, holidays and fiscal quarters

JS NETWORKDAYS, WORKDAY.INTL and fiscal quarter

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Python and pandas equivalents

When you move data out of Excel into Python, the same ideas exist in datetime, pandas and NumPy. This runs in real Python in your browser:

Python The same date logic in pandas and NumPy

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Date function reference: WEEKNUM, YEARFRAC and friends

A few date functions come up less often than DATEDIF or EDATE but cause real confusion when you need them. Excel cannot run on this site, so the formulas are real Excel but read-only; the TryIt after this section is a JavaScript stand-in that reproduces each function's rules so you can check examples.

WEEKNUM and ISOWEEKNUM

WEEKNUM(date, [return_type]) returns the week of the year, but "week 1" depends on a convention:

  • return_type 1 (the default): weeks start on Sunday, and week 1 is the week containing 1 January. Use 2 for Monday starts, or 11 to 17 for other first days (11 Monday, 12 Tuesday... 17 Sunday).
  • return_type 21: the ISO 8601 system, where weeks start on Monday and week 1 is the week with the first Thursday of the year. ISOWEEKNUM(date) is the same thing.

The two systems disagree near the new year: 1 January may be week 1 in the default system but week 52 or 53 of the previous ISO year, and 29 December can already be ISO week 1 of the next year. Pick one convention for the whole workbook and say which.

WEEKDAY return types

WEEKDAY(date, [return_type]) returns a number for the day of the week. With 1 (default) Sunday is 1 and Saturday is 7; with 2 Monday is 1 and Sunday is 7; with 3 Monday is 0 and Sunday is 6; types 11 to 17 make Monday to Sunday the first day, numbered 1 to 7. For names use TEXT(date, "dddd"). A classic test for weekends: =WEEKDAY(A2, 2) > 5.

YEARFRAC and the day-count basis

YEARFRAC(start, end, [basis]) returns the fraction of a year between two dates, which depends on how days are counted:

Basis Method
0 (default) US (NASD) 30/360: every month has 30 days
1 Actual days / actual days in the year
2 Actual days / 360
3 Actual days / 365
4 European 30/360

Bonds and loans specify a basis in their contract; for plain age or tenure use DATEDIF or basis 1. A decimal age is =YEARFRAC(B2, TODAY(), 1), and a whole-year age is =DATEDIF(B2, TODAY(), "y") (Differences: subtraction and DATEDIF).

DATEVALUE and text to date

Dates imported as text will not sort or subtract. =DATEVALUE("2025-03-31") converts a text date into a serial number (format the cell as a date to see it). It depends on your regional settings: "03/04/2025" is 3 April in many countries and 4 March in the US. Safer is to build the date from parts: =DATE(LEFT(A2,4), MID(A2,6,2), RIGHT(A2,2)), which works for ISO text in any locale. TIMEVALUE does the same for times, and Data > Text to Columns > Date converts a whole column in place. A cell that is right-aligned and sorts correctly is a real date; a left-aligned one is text.

Adding and subtracting dates

Dates are numbers, so =A2 + 30 is 30 days later and =B2 - A2 is the number of days between. Use EDATE and EOMONTH for months (Adding months), WORKDAY for business days, and DATE(YEAR(A2)+1, MONTH(A2), DAY(A2)) for the same day next year. Subtracting two times of day returns a fraction of a day: multiply by 24 for hours, =(B2-A2)*24.

WORKDAY.INTL weekend codes

WORKDAY.INTL(start, days, [weekend], [holidays]) and NETWORKDAYS.INTL accept a weekend argument: a number code (1 Saturday-Sunday, 2 Sunday-Monday, 3 Monday-Tuesday, ... 7 Friday-Saturday, 11 Sunday only, 12 Monday only ... 17 Saturday only) or a seven-character string of 0 (working) and 1 (off), starting on Monday: "0000011" is Saturday and Sunday off, "0000001" only Sunday. A string is the flexible choice for odd schedules such as "Friday and Saturday off" ("0000110").

JS WEEKNUM, ISOWEEKNUM, WEEKDAY, YEARFRAC and WORKDAY.INTL

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Adding 30 or 365. Use EDATE for months and DATE(YEAR(A2) + 1, MONTH(A2), DAY(A2)) or EDATE(A2, 12) for years. EDATE(A2, 12) also handles February 29 sensibly.
  • Dates stored as text. =B2-A2 gives #VALUE! or a wrong answer when a date is really text. See number-as-text problems.
  • Forgetting that NETWORKDAYS is inclusive. Monday to Friday is 5, not 4.
  • A holiday range that contains text or blanks. Keep it to real dates only.
  • TODAY() in a historical report. TODAY() is volatile and changes every day. For a report "as of" a date, store the date in a cell.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does =EDATE(DATE(2025,1,31), 1) return?

    EDATE moves by calendar months and clamps to the last valid day of the target month, so January 31 plus one month is February 28 in 2025.
  2. 2. Which formula gives the last day of the month for a date in A2?

    EOMONTH with 0 months returns the last day of the month that contains the date.
  3. 3. A hotel is closed on Sundays only. Which function and weekend argument count its working days?

    The weekend text starts on Monday, and 1 marks a non-working day, so "0000001" makes only Sunday a day off. NETWORKDAYS alone always treats Saturday and Sunday as the weekend.
  4. 4. Why is =(TODAY()-B2)/365 a poor way to calculate age?

    A year is not always 365 days, and the fraction is not a whole number of years. DATEDIF with "Y" counts complete years correctly.
  5. 5. What does DATEDIF return if the start date is later than the end date?

    DATEDIF requires the start to be on or before the end, and returns #NUM! otherwise. Plain subtraction is the way to get a negative difference.

Summary

  • Subtract dates for days; use DATEDIF with "Y", "M" or "YM" for whole units, and avoid "MD".
  • EDATE adds calendar months and clamps to the end of short months; EOMONTH returns month-end dates.
  • NETWORKDAYS counts both ends; WORKDAY does not count the start date. Both accept a holiday range.
  • The .INTL versions take a weekend code or a Monday-first text of 0 and 1.
  • Fiscal quarters come from MOD(MONTH - FYStart, 12); pandas and NumPy have matching tools.

Next: Statistics for Analysts: Averages, Spread and Rank.