Today and Now for Live Dates

Before we make a cell show today's date, it helps to know a secret: spreadsheets store dates as numbers. Each day is counted from a starting point. In Excel and Google Sheets, the number 1 means a fixed first day, and every later day adds one.

That is why you can subtract two dates to count days. The pretty MM/DD/YYYY you see on screen is just formatting laid over a plain number.

In this lesson you will learn two functions that ask the spreadsheet for the current moment: TODAY() and NOW().

The TODAY() function returns the current date with no time attached. It takes no arguments at all, which is why the parentheses are empty.

Type it into any cell and press Enter. The cell fills with today's calendar date, formatted as a date automatically.

Because it has no inputs, you must still include the empty (). Writing just =TODAY would be treated as a name, not a function call.

The most important feature of TODAY() is that it is live. It is a volatile function, meaning the spreadsheet recalculates it whenever the file is opened or any change forces a recalculation.

If you build a report today and open it next week, every =TODAY() cell will show the new date by itself. You never edit the formula.

This makes it perfect for headers like Report generated on: that should always feel current.

Sometimes you need the time too, not just the date. The NOW() function returns the current date and time as a single value.

Like TODAY, it takes no arguments, so you write empty parentheses. The underlying number has a whole part for the day and a fractional part for the time of day.

For example, 0.5 as a fraction of a day equals noon, because noon is halfway through the 24 hours.

Because NOW() contains both date and time, you can format the cell to show only the part you care about. Apply a time format such as h:mm AM/PM and the date portion stays hidden behind the scenes.

You can also subtract the whole-number part to keep only the time fraction. The function INT drops the decimals, so NOW()-INT(NOW()) leaves just the time of day.

Here is the quick rule:

• TODAY() gives the date only (midnight of the current day).
• NOW() gives the date and the current time.

If you only need to count days or stamp a calendar date, use TODAY. If you are logging an exact moment, like a timestamp, use NOW.

Both refresh automatically because both are volatile functions.

Imagine a project deadline sits in cell B2 as a real date. You want a cell that always shows how many days are left, counting from today.

Subtract today's date from the deadline. Because dates are numbers, the result is a plain count of days that updates every day on its own.

If the deadline is in 10 days, the cell shows 10. Tomorrow it will show 9 with no edits from you.

Since dates are numbers, you can add a number of days directly to TODAY(). This is great for due dates.

To find the date one week from now, add 7. For 30 days from now, add 30. The result is automatically a date.

This pattern is everywhere in real spreadsheets: invoice due dates, follow-up reminders, and trial expirations all use TODAY plus a number of days.

Because TODAY and NOW refresh constantly, they are the wrong choice when you want to record when something happened and freeze it.

For a permanent timestamp, type the value once and convert it to a fixed value. In Excel, the shortcut Ctrl + ; inserts a static date, and Ctrl + Shift + ; inserts a static time.

These hard-coded values never change, unlike the live functions.

Let us combine ideas into one polished header. We will join text with a live date using the & operator and the TEXT function so the date reads nicely.

The TEXT function wraps a number in a display pattern. Here "DDDD, MMMM D" spells out the weekday and month, for example Wednesday, June 18.

The whole header updates itself every day the file is opened.

One thing surprises new users: a volatile function like NOW() updates whenever anything in the sheet recalculates, not just at a fixed interval.

So if you put =NOW() in a cell and then type a value somewhere else, the timestamp jumps to the new current time. It is not a clock that ticks; it refreshes on recalculation.

If you need a true moving clock, that requires a script or macro, which is beyond plain formulas.

Test what you learned about live date functions.

You learned how to put the current moment into a cell:

• Spreadsheets store dates as numbers, so you can add and subtract them.
• TODAY() returns the date only and refreshes automatically.
• NOW() returns the date and time and is also volatile.
• Add days directly, like =TODAY()+30, for due dates.
• For a frozen timestamp, type a static value with Ctrl + ; instead of a live function.

Next you will pull individual pieces out of a date.