TODAY and NOW in Google Sheets - Deep Dive

Overview - TODAY and NOW

What is it?

TODAY and NOW are special functions in Google Sheets that give you the current date and time. TODAY returns only the current date without any time. NOW returns both the current date and the exact current time. These functions update automatically whenever the spreadsheet recalculates.

Why it matters

These functions help you track real-time information like deadlines, timestamps, or durations without typing the date or time manually. Without them, you would have to update dates and times yourself, which is slow and error-prone. They make your sheets dynamic and always up-to-date.

Where it fits

Before learning TODAY and NOW, you should understand basic spreadsheet cells and how to enter simple formulas. After mastering these, you can learn about date and time calculations, conditional formatting based on dates, and scheduling tasks automatically.

Mental Model

Core Idea

TODAY and NOW are like a live clock and calendar inside your spreadsheet that always show the current date or date plus time.

Think of it like...

Imagine a wall calendar and a clock in your room. TODAY is like looking at the calendar to see today's date. NOW is like glancing at the clock and calendar together to see the exact date and time right now.

┌───────────────┐
│   TODAY()     │
│  Returns:     │
│  2024-06-15   │
└───────────────┘
       ↓
Shows only the current date

┌───────────────┐
│   NOW()       │
│  Returns:     │
│  2024-06-15   │
│  14:30:45     │
└───────────────┘
       ↓
Shows current date and time

Build-Up - 7 Steps

1

Foundation

Understanding TODAY function basics

Concept: TODAY returns the current date without time.

Type =TODAY() in any cell. It will show the current date like 2024-06-15. It updates automatically each day when you open or recalculate the sheet.

Result

Cell shows today's date, for example, 2024-06-15.

Knowing TODAY gives you a simple way to always have the current date without typing it manually.

2

Foundation

Understanding NOW function basics

Concept: NOW returns the current date and time together.

Type =NOW() in any cell. It will show the current date and time like 2024-06-15 14:30:45. It updates every time the sheet recalculates or opens.

Result

Cell shows current date and time, for example, 2024-06-15 14:30:45.

NOW lets you track the exact moment something happens, not just the day.

3

Intermediate

Difference between TODAY and NOW

🤔Before reading on: Do you think TODAY includes time or just date? Commit to your answer.

Concept: TODAY returns only date; NOW returns date and time.

TODAY() always shows date only, like 2024-06-15. NOW() shows date plus time, like 2024-06-15 14:30:45. Use TODAY when time is not important, NOW when you need exact time.

Result

You can choose the right function depending on whether you want time included.

Understanding this difference helps you avoid confusion and pick the right function for your needs.

4

Intermediate

How automatic updates work

🤔Before reading on: Do you think TODAY and NOW update every second or only when you open the sheet? Commit to your answer.

Concept: These functions update when the sheet recalculates or opens, not every second.

TODAY and NOW refresh their values when you open the sheet or when any change triggers recalculation. They do not update continuously like a live clock. This keeps sheets efficient.

Result

Dates and times stay current but do not slow down your sheet with constant updates.

Knowing when updates happen prevents surprises about stale or delayed times in your sheet.

5

Intermediate

Using TODAY and NOW in calculations

🤔Before reading on: Can you subtract TODAY from another date to find days difference? Commit to your answer.

Concept: You can use TODAY and NOW in formulas to calculate durations or deadlines.

Example: =A1 - TODAY() gives days between date in A1 and today. =NOW() - A2 gives time difference including hours and minutes. These help track how much time has passed or is left.

Result

You get numbers showing days or fractions of days between dates and times.

Using these functions in math lets you automate time tracking and reminders.

6

Advanced

Formatting results for clarity

🤔Before reading on: Does TODAY() show time if you format the cell to show time? Commit to your answer.

Concept: Cell formatting controls how date and time appear, but underlying values stay the same.

TODAY returns a date with time zero (midnight). If you format the cell to show time, it will show 00:00. NOW returns date and time. You can format cells to show only date, only time, or both for clarity.

Result

You control how dates and times look without changing their actual values.

Knowing formatting separates display from data helps avoid confusion about what the function returns.

7

Expert

Performance and recalculation nuances

🤔Before reading on: Do you think using many NOW() functions slows down your sheet significantly? Commit to your answer.

Concept: TODAY and NOW recalculate on every change, which can affect performance if overused.

Each NOW or TODAY recalculates when the sheet changes, which can slow large sheets. To optimize, use them sparingly or copy-paste values if you don't need constant updates. Also, NOW updates only on recalculation, not continuously.

Result

You balance real-time accuracy with sheet speed and avoid slowdowns.

Understanding recalculation behavior helps you design efficient, responsive spreadsheets.

Under the Hood

TODAY and NOW are volatile functions that query your device's system clock when the sheet recalculates. TODAY strips the time portion, returning only the date as a serial number representing days since a fixed start date. NOW returns the full date-time serial number including fractional days for time. The spreadsheet engine updates these values on open or when any cell changes, triggering recalculation.

Why designed this way?

They were designed to provide dynamic current date and time without manual input, improving automation. The choice to update only on recalculation balances accuracy with performance, preventing constant recalculation that would slow sheets. Separating TODAY and NOW allows users to choose between date-only or date-time precision.

┌───────────────┐
│ System Clock  │
└──────┬────────┘
       │
       ▼
┌───────────────┐       ┌───────────────┐
│ Spreadsheet   │       │ Spreadsheet   │
│ Engine        │       │ Engine        │
│ (Recalculation)│─────▶│ (Recalculation)│
└──────┬────────┘       └──────┬────────┘
       │                       │
       ▼                       ▼
┌───────────────┐       ┌───────────────┐
│ TODAY()       │       │ NOW()         │
│ Returns date  │       │ Returns date  │
│ only          │       │ and time      │
└───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does TODAY() include the current time? Commit to yes or no.

Common Belief: TODAY() returns the current date and time together.

Reality: TODAY() returns only the date with time set to midnight (00:00). It does not include the current time.

Why it matters: Using TODAY() when you need time can cause errors in time calculations or scheduling.

Quick: Does NOW() update every second automatically? Commit to yes or no.

Common Belief: NOW() updates continuously like a live clock every second.

Reality: NOW() updates only when the sheet recalculates or opens, not every second.

Why it matters: Expecting NOW() to update live can cause confusion when times appear stale.

Quick: If you format a TODAY() cell to show time, will it show the current time? Commit to yes or no.

Common Belief: Formatting a TODAY() cell to show time will display the current time.

Reality: TODAY() has time zero, so formatting to show time will always show 00:00, not the current time.

Why it matters: Misunderstanding formatting leads to wrong assumptions about what the function returns.

Quick: Does using many NOW() functions slow down your sheet significantly? Commit to yes or no.

Common Belief: Using many NOW() functions has no impact on sheet performance.

Reality: Many volatile NOW() functions cause frequent recalculations, which can slow down large sheets.

Why it matters: Ignoring performance impact can make your spreadsheet slow and frustrating to use.

Expert Zone

1

TODAY and NOW return serial numbers internally, which means you can use them in math directly without conversion.

2

The recalculation of NOW and TODAY depends on spreadsheet settings; manual recalculation modes can delay updates.

3

Copy-pasting values from NOW or TODAY cells can freeze the date/time, useful for snapshots but breaks dynamic updates.

When NOT to use

Avoid using NOW or TODAY in very large or complex sheets where performance is critical; instead, use static timestamps or script-based triggers to update dates and times only when needed.

Production Patterns

Professionals use TODAY to create dynamic reports that always show current status, deadlines, or age calculations. NOW is used for timestamping actions or logging events with exact time. Often combined with conditional formatting to highlight overdue tasks or upcoming events.

Connections

Unix Timestamp

Both represent points in time as numbers counting from a fixed start date.

Understanding how TODAY and NOW convert dates and times to serial numbers helps grasp how Unix timestamps count seconds from 1970-01-01.

Event Scheduling

TODAY and NOW provide the current time reference needed to trigger or check scheduled events.

Knowing how to use these functions helps automate reminders, deadlines, and alerts in scheduling systems.

Real-Time Clocks in Embedded Systems

TODAY and NOW act like software versions of hardware clocks that provide current date and time.

This connection shows how software mimics hardware clocks to keep time, bridging spreadsheet functions and embedded device design.

Common Pitfalls

#1 Expecting NOW() to update continuously like a live clock.

Wrong approach:

=NOW() used in many cells expecting real-time second updates.

Correct approach:

=NOW() used sparingly with manual refresh or script triggers for updates.

Root cause:

Misunderstanding that NOW updates only on recalculation, not every second.

#2 Using TODAY() when time precision is needed.

Wrong approach:

=TODAY() used to timestamp events requiring exact time.

Correct approach:

=NOW() used to capture date and time for precise timestamps.

Root cause:

Confusing TODAY as including time leads to loss of time detail.

#3 Formatting TODAY() cells to show time expecting current time display.

Wrong approach:

Cell with =TODAY() formatted as date and time shows 00:00 time.

Correct approach:

Use =NOW() if time display is needed, or format TODAY() cells as date only.

Root cause:

Not realizing TODAY returns time zero, so formatting cannot add real time.

Key Takeaways

TODAY() returns the current date only, with time set to midnight, and updates on sheet recalculation.

NOW() returns the current date and time together, updating only when the sheet recalculates or opens.

Both functions are volatile and can slow down large sheets if overused due to frequent recalculations.

Formatting controls how date and time appear but does not change the underlying value returned by these functions.

Using TODAY and NOW in calculations automates time tracking, deadlines, and timestamps, making spreadsheets dynamic and useful.