Working with dates
8 minutes · 5 steps · free, no sign-up
A date in a spreadsheet is really a number: the count of days since the end of 1899. That is why you can add and subtract dates, and why a date sometimes appears as a strange five-digit number until you format it. This lesson shows you how to build, read and display dates.
- Build a date from a year, month and day with DATE
- Format a number as a date
- Read the year, month, day and weekday back out
- Write a date in words with TEXT
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=DATE(year, month, day)
=YEAR(date) =MONTH(date) =DAY(date)
=WEEKDAY(date, 2) Monday = 1 ... Sunday = 7
=TEXT(date, "dddd d mmmm yyyy")
=TODAY() today, updates dailyThe key ideasSummary: Working with dates
Every date is a serial number: 1 is 31 December 1899, and each day adds one. Formatting only decides how that number looks. Format the same cell as a number and you will see it, which is a handy way to check that a "date" really is one and not just text.
DATE(year, month, day) is the safest way to build a date, because it does not depend on how your region writes dates (1/2/2026 means different days in the UK and the US). YEAR, MONTH and DAY take a date apart again, and TEXT turns it into words such as "Saturday 14 March 2026".
Watch out forCommon mistakes
- Typing dates as text. A date left-aligned in its cell is usually text and will not calculate. Build it with DATE or retype it in a form the sheet recognises.
- Mixing up day and month order. 3/4/2026 is 3 April in the UK and 4 March in the US. DATE removes the doubt.
- Expecting TEXT to stay a date. The result of TEXT is plain text. Keep the real date in one cell and use TEXT only for display.
- Using TODAY for a fixed date. TODAY changes every day. Use it for "days until", never to record when something happened.
Next stepTry it in the real Google Sheets
In Google Sheets, Format → Number → Date applies a date format, and the format codes in TEXT (dd, mmm, yyyy) follow the same pattern. Excel and Sheets share the same idea, with slightly different starting dates, so serial numbers can differ.