Spreadsheet Practice

Lesson 17 of 29, Dates

DATE, YEAR, MONTH & TEXT

A date is a number wearing a costume.

Pro8 minutes, 3 exercisesDATEYEARMONTHTEXT

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Excel stores a date as the count of days since January 1, 1900, so 1/15/2026 is 46037. That's why you can subtract two dates to get days between them.

YEAR, MONTH and DAY pull the parts out. DATE(year, month, day) builds a date from parts. TEXT(date, "mmm") turns a date into a label such as Jan.

Syntax

=YEAR(A2)2026
=MONTH(A2)1
=TEXT(A2,"mmm")Jan
=TEXT(A2,"dddd")Thursday
=DATE(F2,G2,H2)Build a date from parts

Watch out for

  • TEXT returns text, so sort and filter on the real date, not the label.
  • DATE rolls over: DATE(2026,13,1) is January 1, 2027.

The exercises

  1. Year and month. Fill B2:B6 with the year and C2:C6 with the month number of each invoice date.
  2. Month labels. In D2, show the three-letter month name (Jan, Feb…) for each invoice date. Fill down to D6.
  3. Build due dates. In I2, build a real date from the year, month and day in F2:H2. Fill down to I6.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Year and month
  2. Month labels
  3. Build due dates
Try these functions in the Formula Lab