Date Functions in Excel: DATEDIF, NETWORKDAYS, EOMONTH

The short answer

Excel stores a date as a serial number of days; what you see is just the cell format. So:

• a deadline 30 calendar days out is =A2+30;
• working days between dates come from NETWORKDAYS;
• the date N working days later comes from WORKDAY;
• full years and months between dates come from DATEDIF;
• the last day of a month comes from EOMONTH.

The same functions work in Google Sheets. Depending on your regional settings, arguments may be separated by semicolons instead of commas.

Deadlines in calendar and working days

A calendar deadline is plain addition:

=A2+14

A deadline in working days that skips weekends and holidays. Keep the holiday list on a separate sheet and give the range a name such as Holidays:

=WORKDAY(A2, 10, Holidays)

WORKDAY does not count the start date itself: counting begins on the next day.

Six-day week. If only Sunday is a day off, use the .INTL versions. A seven-character string describes the week starting Monday, where 1 marks a day off:

=WORKDAY.INTL(A2, 10, "0000001", Holidays)

Working days between dates

=NETWORKDAYS(A2, B2, Holidays)
=NETWORKDAYS.INTL(A2, B2, "0000001", Holidays)

NETWORKDAYS includes both dates, start and end, if they are working days. This is the usual reason for an off-by-one difference from a manual count.

Update the holiday list every year: some holidays follow the lunar calendar, and days off can be moved by official decisions.

Age and tenure: DATEDIF

DATEDIF does not appear in Excel's formula suggestions, but it works. The third argument sets the unit:

Age as of today:

=DATEDIF(B2, TODAY(), "Y")

Tenure shown as "3 y 7 mo":

=DATEDIF(B2, TODAY(), "Y") & " y " & DATEDIF(B2, TODAY(), "YM") & " mo"

Important: the start date must be earlier than the end date, or you get #NUM!. The "MD" code can return incorrect results in some cases, and Microsoft advises against relying on it.

Month ends: EOMONTH

The second argument is an offset in months: 0 is the current month, 1 the next, -1 the previous.

=EOMONTH(A2, 0)         last day of the month
=EOMONTH(A2, -1)+1      first day of the month
=EOMONTH(A2, 1)         payment due by the end of next month
=DAY(EOMONTH(A2, 0))    number of days in the month

If the result shows as a number like 46387, it is not an error: just apply a date format to the cell. To shift by whole months while keeping the day, use EDATE.

Date formats

• Cell formatting (Ctrl+1) changes only the display; the value stays a date and you can still calculate with it.
• TEXT turns a date into a string for labels: =TEXT(A2, "dd.mm.yyyy"). Format codes depend on the Excel language; in Russian Excel, for example, they are "ДД.ММ.ГГГГ".
• Do not use TEXT in cells you later calculate with: the result is already text.

Dates stored as text

Signs: dates are left-aligned, sorting goes "alphabetically", and =ISNUMBER(A2) returns FALSE.

Ways to fix it:

1. Text to Columns. Select the column, choose Data, Text to Columns, and on the last step pick the Date format with the DMY order.
2. DATEVALUE: =DATEVALUE(A2) works when the text matches your regional date format.
3. Build it from parts for strings like "25.12.2026", independent of settings:

=DATE(RIGHT(A2, 4), MID(A2, 4, 2), LEFT(A2, 2))

Before that, remove extra spaces with TRIM and non-breaking spaces from exports with SUBSTITUTE.