Date Functions

Dates are everywhere in business data — order dates, due dates, hire dates, invoice dates, deadlines. And Excel handles them in a surprisingly powerful way once you understand what is going on under the hood. In this lesson we cover how dates are stored, how to work with them in formulas, and the essential date functions that make time-based calculations fast and accurate.

How Excel Stores Dates

We touched on this in Lesson 7 — Excel stores every date as a serial number. The number 1 represents 1 January 1900, and every day since then adds one to the count. So today's date (8 March 2026) is stored as the number 45723.

This serial number system is what makes date arithmetic work. Because dates are just numbers, you can add, subtract, and compare them exactly like any other number. Subtracting two dates gives you the number of days between them. Adding 30 to a date gives you the date 30 days later.

TODAY and NOW — The Live Clock Functions

These two functions return the current date or date-and-time. They take no arguments and recalculate automatically every time the workbook is opened or recalculated. They are the foundation of any date formula that needs to reference "right now."

=TODAY()     Returns today's date as a date value (no time)
=NOW()       Returns today's date AND current time

Because TODAY() returns a number (today's serial date), you can use it in arithmetic. =TODAY()-A2 gives you the number of days since the date in A2. =A2-TODAY() gives you the days until a future date. These are incredibly common in dashboards and project trackers.

Extracting Date Parts — DAY, MONTH, YEAR, WEEKDAY

Once you have dates in your data, you often need to extract a specific component — the year for grouping, the month for a chart, the weekday to identify Mondays. These functions pull out individual parts from any date value.

Date in A2: 15/03/2025

=DAY(A2)       →  15      (the day number)
=MONTH(A2)     →  3       (the month number — March = 3)
=YEAR(A2)      →  2025    (the four-digit year)
=WEEKDAY(A2)   →  7       (7 = Saturday, using Sunday=1 default)
=WEEKDAY(A2,2) →  6       (6 = Saturday, using Monday=1 system)

WEEKDAY has a second argument that controls which day is treated as day 1. The most common setting for business use is 2, which makes Monday = 1 and Sunday = 7 — matching the standard work week. You will often see WEEKDAY used inside IF formulas to flag or skip weekends.

Building Dates — DATE, TIME, DATEVALUE

Sometimes date components live in separate columns — year in one, month in another, day in a third. The DATE function assembles them into a proper date serial number that Excel recognises and can calculate with.

=DATE(year, month, day)

=DATE(2025, 3, 15)          →  15/03/2025
=DATE(A2, B2, C2)           →  Builds date from separate cells
=DATE(YEAR(A2), MONTH(A2)+1, 1)  →  First day of next month

DATEVALUE converts a date stored as text (like "15/03/2025" typed as a string) into a proper date serial number. This comes up constantly when importing data from other systems where dates arrive as text and formulas cannot calculate with them.

=DATEVALUE("15/03/2025")     →  45730   (now a real date number)
=DATEVALUE(A2)               →  Converts text date in A2 to a number

Calculating Date Differences — DATEDIF

Simple subtraction gives you the number of days between two dates. But what if you need the difference in months, or years, or a combination like "2 years, 4 months, 11 days"? That is what DATEDIF is for.

DATEDIF is one of Excel's hidden functions — it is not listed in the autocomplete suggestions, but it works in all versions. The syntax is DATEDIF(start_date, end_date, unit) where unit is a code that controls what you are measuring:

=DATEDIF(start_date, end_date, unit)

Units:
  "Y"   →  Complete years between the two dates
  "M"   →  Complete months between the two dates
  "D"   →  Total days between the two dates
  "YM"  →  Months remaining after subtracting complete years
  "MD"  →  Days remaining after subtracting complete months

Example — Employee hired 10 Jun 2019, today is 08 Mar 2026:
  =DATEDIF(A2, TODAY(), "Y")   →  6    (complete years)
  =DATEDIF(A2, TODAY(), "YM")  →  8    (months beyond 6 years)
  =DATEDIF(A2, TODAY(), "MD")  →  28   (days beyond 8 months)

Working With Month Ends — EOMONTH and EDATE

Two functions that come up constantly in finance and reporting: EOMONTH returns the last day of a month, and EDATE moves a date forward or backward by a specified number of months.

=EOMONTH(start_date, months)
=EDATE(start_date, months)

Date in A2: 15/01/2025

=EOMONTH(A2, 0)    →  31/01/2025  (last day of same month)
=EOMONTH(A2, 1)    →  28/02/2025  (last day of next month)
=EOMONTH(A2, -1)   →  31/12/2024  (last day of previous month)

=EDATE(A2, 3)      →  15/04/2025  (same day, 3 months forward)
=EDATE(A2, -6)     →  15/07/2024  (same day, 6 months back)

EOMONTH is particularly useful in financial reporting. Invoice due dates, subscription renewals, and month-end close processes all need the last day of a month — and EOMONTH handles February correctly, including leap years, without you needing to think about it.

Working Days — WORKDAY and NETWORKDAYS

Sometimes you need to calculate dates excluding weekends — when is something due in 10 business days? How many working days are there between two dates? Excel has dedicated functions for this.

=WORKDAY(start_date, days, [holidays])
=NETWORKDAYS(start_date, end_date, [holidays])

Start date in A2: 01/03/2026 (Sunday)

=WORKDAY(A2, 10)              →  13/03/2026  (10 working days later)
=WORKDAY(A2, 10, HolidayList) →  Skips holidays in named range too

=NETWORKDAYS(A2, B2)              →  Working days between A2 and B2
=NETWORKDAYS(A2, B2, HolidayList) →  Excludes holidays as well

Both functions accept an optional third argument — a range containing holiday dates to skip. Store your public holidays in a named range like Holidays and pass it in to get accurate business-day calculations throughout the year.

A Practical Project Tracker

Here is all of the above coming together in a project deadline tracker — the kind of thing used in operations, HR, and finance teams every day:

Next up — Logical Functions, where you will learn how to make Excel make decisions using IF, AND, OR, NOT, IFS, and IFERROR.