Date functions

TODAY, YEAR, MONTH, DATEDIF, EOMONTH and working-day calculations.

What you'll be able to do

Extract date parts
Calculate differences and ages
Handle month ends and working days

Core functions

=TODAY()                   ' today, updates on recalculation
=NOW()                     ' date and time
=YEAR(A2) / =MONTH(A2) / =DAY(A2)
=WEEKDAY(A2, 2)            ' 1 = Monday with return type 2
=TEXT(A2, "mmm")           ' month name
=EOMONTH(A2, 0)            ' last day of that month
=EOMONTH(A2, -1) + 1       ' first day of that month

TODAY() is volatile — it recalculates on every change. On a large workbook a column of them is a real slowdown; put it in one cell and reference that.

DATEDIF

=DATEDIF(A2, TODAY(), "y")    ' complete years — the age calculation
=DATEDIF(A2, B2, "m")         ' complete months
=DATEDIF(A2, B2, "d")         ' days

DATEDIF is undocumented and does not appear in IntelliSense — it is a survivor from Lotus 1-2-3 — but it works in every version and is the correct way to compute an age. Simple subtraction divided by 365 gets leap years wrong.

Working days

=NETWORKDAYS(A2, B2)                  ' working days, excluding weekends
=NETWORKDAYS(A2, B2, Holidays)        ' also excluding a holiday range
=WORKDAY(A2, 10, Holidays)            ' the date 10 working days later
=NETWORKDAYS.INTL(A2, B2, 7)          ' custom weekend definition

NETWORKDAYS counts both endpoints. The .INTL variants matter for regions where the weekend is not Saturday and Sunday.

Grouping into periods

=EOMONTH(A2, 0)                       ' month end, for grouping
=YEAR(A2) & "-" & TEXT(MONTH(A2), "00")   ' 2024-03, sorts correctly

Month names sort alphabetically

=TEXT(A2, "mmm") gives Apr, Aug, Dec. Use a zero-padded year-month string, or keep a real date and format it, so sorting stays chronological.

Key points

DATEDIF is undocumented but is the correct age calculation
NETWORKDAYS excludes weekends and an optional holiday list
Group by a real date or a zero-padded string, not a month name

Check yourself