Date functions

Dates are numbers

Excel stores a date as a serial number: the number of days since 1 January 1900, which is day 1.

Format a date cell as General, and you'll see its number. That's what makes date maths easy: one day is 1, so adding 7 moves a week forward, and subtracting one date from another counts the days between them. A time of day is a fraction of a day: 12:00 noon is 0.5.

Building and taking apart dates

=DATE(2024, 7, 1)          → 1 July 2024
=YEAR(A2)                  → 2024
=MONTH(A2)                 → 7
=DAY(A2)                   → 1
=WEEKDAY(A2)               → 2       Sunday is 1, so Monday is 2
=WEEKDAY(A2, 2)            → 1       with 2, Monday is 1 and Sunday is 7

(Here A2 holds 1 July 2024, a Monday.)

DATE(year, month, day) is the reliable way to put a date into a formula. Typing "01/07/2024" is ambiguous: is that 1 July or 7 January? It depends on the computer's regional settings.

DATE also corrects overflowing values, which is handy: =DATE(2024, 13, 1) gives 1 January 2025, and =DATE(2024, 3, 0) gives the last day of February.

Today and now

=TODAY()                   → today's date
=NOW()                     → today's date and the current time
=TODAY() - A2              → days since the date in A2

TODAY and NOW recalculate whenever the workbook changes or opens. To stamp a fixed date that never changes, press Ctrl+; instead.

Adding days, months and years

=A2 + 30                   → 31 July 2024         30 days later
=EDATE(A2, 1)              → 1 August 2024        one month later
=EDATE(DATE(2024,1,31), 1) → 29 February 2024     no 31 February, so the month's last day
=EOMONTH(A2, 0)            → 31 July 2024         the end of the same month
=EOMONTH(A2, 1)            → 31 August 2024       the end of next month

Use EDATE for "the same day next month" (subscriptions, loan payments) and EOMONTH for month-end deadlines.

The difference between two dates

=DATEDIF(DATE(2000,3,15), DATE(2024,7,1), "Y")    → 24     whole years: an age
=DATEDIF(DATE(2000,3,15), DATE(2024,7,1), "M")    → 291    whole months
=DATEDIF(DATE(2000,3,15), DATE(2024,7,1), "D")    → 8874   days

DATEDIF(start, end, unit) is an old function that still works in every version of Excel, although Excel won't suggest it as you type. For an age, use "Y" with TODAY() as the end date.

Working days

=NETWORKDAYS(DATE(2024,7,1), DATE(2024,7,31))            → 23    weekdays in July 2024
=NETWORKDAYS(DATE(2024,7,1), DATE(2024,7,31), H2:H4)     → fewer, skipping holidays listed in H2:H4
=WORKDAY(A2, 10)                                         → the date 10 working days after A2

NETWORKDAYS counts Monday to Friday, including both ends, and leaves out any holidays you list. WORKDAY moves forward by working days, which suits delivery dates and project plans.

Formatting dates

The value stays the same; only the display changes:

=TEXT(A2, "dd-mm-yyyy")      → "01-07-2024"
=TEXT(A2, "d mmm yyyy")      → "1 Jul 2024"
=TEXT(A2, "dddd")            → "Monday"
=TEXT(A2, "mmmm yyyy")       → "July 2024"

Use these codes in Format Cells → Custom to format date cells directly, or in TEXT to build sentences like ="Due on " & TEXT(A2, "d mmm").