Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 29 ยท Lookups and Dates

Dates and Times

Learn how Google Sheets stores dates as serial numbers and use TODAY, DATE, EDATE, DATEDIF, NETWORKDAYS, WEEKDAY and TEXT to calculate with them.

Dates look like text, but Google Sheets treats them as numbers. That one fact explains why you can subtract two dates to get a number of days, why a date can suddenly show as 45292, and why sorting dates just works. Once you see the number behind the date, date math becomes ordinary arithmetic.

In this lesson you will learn the serial-number system, build dates with DATE, move by months with EDATE and EOMONTH, measure gaps with DATEDIF and NETWORKDAYS, find weekdays, and format dates with TEXT. If cell formatting is new to you, read Formatting Cells first.

A date is a number

Serial number Days counted from 30 Dec 1899.
Format Decides how the number looks.
Subtract End minus start gives days.

Change the format and the same number shows as a date or a plain number.

1 / 2

Serial numbers

Sheets counts days from 30 December 1899, which is day 0. Day 1 is 31 December 1899, and 1 January 2024 is day 45292. Times are the fraction of a day: 0.5 is noon, 0.25 is 6:00 in the morning. So 45292.5 means 1 January 2024 at 12:00.

You can see the number behind any date by selecting the cell and choosing Format โ†’ Number โ†’ Number (or Automatic, then back). Typing =A2+30 in a cell next to a date gives a date thirty days later, because adding 30 to the serial number moves 30 days forward. Subtracting works the same way: =B2-A2 is the number of days between two dates (format the result as a number, not a date, if Sheets shows something like 1/14/1900).

The Sheets app cannot run on this page. The Python blocks below are stand-ins that reproduce the same date arithmetic, so you can check numbers such as 45292 yourself. The formulas shown in plain blocks are real Sheets formulas.

Python Stand-in: serial numbers in Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Building and reading dates

Never type a date as loose text if a formula must read it. Build it from numbers instead:

TEXT
=DATE(2024, 3, 15)      the date 15 March 2024
=TODAY()                today's date, recalculated when the sheet recalculates
=NOW()                  today's date and the current time
=YEAR(A2)  =MONTH(A2)  =DAY(A2)   take a date apart

DATE is forgiving: it rolls over out-of-range parts. =DATE(2024, 14, 1) is 1 February 2025, and =DATE(2024, 3, 0) is the last day of February 2024. TODAY() and NOW() are volatile: they change every time the sheet recalculates, so a "days until deadline" formula built on them updates by itself, but a date you want to keep must be typed or pasted as a value (Ctrl+Shift+V).

Dates entered with the keyboard depend on the spreadsheet's locale (File โ†’ Settings). In a US locale 3/4/2024 means 4 March; in the UK it means 3 April. DATE(year, month, day) has no such ambiguity.

Moving by months

Adding 30 is fine for days, but months have different lengths. Use these instead:

Formula Result for 2024-01-31
=EDATE(A2, 1) 29 Feb 2024 (clamped to the last valid day)
=EDATE(A2, -2) 30 Nov 2023
=EOMONTH(A2, 0) 31 Jan 2024 (end of the same month)
=EOMONTH(A2, 1) 29 Feb 2024 (end of next month)

EDATE keeps the day number when it can and clamps it when the target month is shorter. EOMONTH is the standard trick for "first day of next month": =EOMONTH(A2, 0) + 1.

Python Stand-in: EDATE and EOMONTH

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Measuring gaps

For a plain number of days, subtract. For whole years, months or days use DATEDIF(start, end, unit):

TEXT
=DATEDIF(A2, B2, "Y")    complete years
=DATEDIF(A2, B2, "M")    complete months
=DATEDIF(A2, B2, "D")    days
=DATEDIF(A2, B2, "YM")   months left over after whole years
=DATEDIF(A2, B2, "MD")   days left over after whole months

A classic use is age: =DATEDIF(birthday, TODAY(), "Y"). The start date must not be later than the end date, or you get #NUM!. A readable age such as "5 years, 3 months" joins the pieces: =DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months".

NETWORKDAYS(start, end, [holidays]) counts working days, Monday to Friday, including both the start and end dates. Give it a range of holiday dates as the third argument to skip them. WORKDAY(start, days, [holidays]) goes the other way: it returns the date that is a given number of working days away. If your weekend differs, use NETWORKDAYS.INTL and WORKDAY.INTL.

Python Stand-in: whole years, months and working days

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Weekdays and TEXT formats

WEEKDAY(date) returns 1 for Sunday through 7 for Saturday by default. WEEKDAY(date, 2) makes Monday 1 and Sunday 7, which is easier for "is it a weekend?" tests: =WEEKDAY(A2, 2) > 5.

TEXT(value, format) turns a date into text in the layout you choose:

Formula Shows
=TEXT(A2, "yyyy-mm-dd") 2024-03-15
=TEXT(A2, "dddd") Friday
=TEXT(A2, "ddd, mmm d") Fri, Mar 15
=TEXT(A2, "mmmm yyyy") March 2024

Remember that TEXT returns text, not a date, so use it for labels and keep the original cell for calculations. Combine it with other text, as in ="Due on " & TEXT(A2, "mmmm d").

Python Stand-in: WEEKDAY and TEXT formats

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common mistakes

  • A date shows as a number. The cell has a number format. Use Format โ†’ Number โ†’ Date.
  • A date is left-aligned. It was read as text, so formulas skip it. Re-enter it or wrap it in DATEVALUE.
  • Day counts are off by one. Subtraction gives the gap; NETWORKDAYS is inclusive, so a Monday-to-Monday range counts both Mondays.
  • The date changes each day. You used TODAY() where a fixed date was needed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a date cell actually store?

    Sheets stores dates as serial numbers (days from 30 Dec 1899). Formatting decides how that number is displayed.
  2. 2. Which formula gives the last day of the month of the date in A2?

    EOMONTH with 0 months returns the end of the same month.
  3. 3. =DATEDIF(A2, B2, "Y") returns what?

    The unit "Y" counts complete years. Use "M" for months and "D" for days.
  4. 4. =NETWORKDAYS(A2, B2) where A2 is a Monday and B2 is the Friday of the same week returns what?

    NETWORKDAYS counts working days inclusively, so Monday, Tuesday, Wednesday, Thursday and Friday give 5.

Summary

  • Dates are serial numbers (day 0 is 30 Dec 1899); times are fractions of a day.
  • Build dates with DATE(year, month, day) to avoid locale confusion; TODAY() and NOW() are volatile.
  • EDATE moves by months and clamps short months; EOMONTH finds month ends.
  • DATEDIF measures whole years, months or days; NETWORKDAYS counts working days inclusively.
  • WEEKDAY(date, 2) numbers Monday as 1; TEXT formats dates into labels.

Next: Sorting and Filtering Data