How do I insert today's date into a formula, and calculate how many days have passed since another date?
TODAY() returns the current date, and NOW() returns the current date and time, both updating automatically whenever the sheet recalculates. Subtract a stored date from TODAY() to get the number of days elapsed: =TODAY()-A2.
Formula (Excel and Google Sheets)
=TODAY()-A2TODAY() and NOW() do not take any arguments; they simply return the current date (TODAY) or the current date and time (NOW) at the moment the sheet last recalculated. Because they update automatically, a formula built on TODAY() or NOW() will show a different result tomorrow, which is exactly what you want for something like "days since last update" but not what you want if you need to record a fixed date permanently.
Subtracting a stored date from TODAY(), as in =TODAY()-A2, gives the number of days between that date and today, formatted as a plain number. Wrapping the same idea in DATEDIF(A2,TODAY(),"Y") instead gives the number of complete years, which is the usual way to calculate an age from a birth date.
Step by step
- 1
Use TODAY() for a date, NOW() for a date and time
Type either directly into a cell with no arguments: =TODAY() or =NOW().
- 2
Subtract a stored date to get days elapsed
=TODAY()-A2, where A2 holds the earlier date. Format the result cell as a number, not a date.
- 3
Use DATEDIF for a year-based age
=DATEDIF(A2,TODAY(),"Y") gives complete years elapsed since the date in A2.
Common questions
Why does my sheet keep showing a different date every day?
That is expected behavior: TODAY() and NOW() recalculate automatically. If you need a date that stays fixed, type or paste it as a value instead of using TODAY().
Why does =TODAY()-A2 show a date instead of a number?
The result cell inherited date formatting. Change the cell's format to Number to see the day count instead of a date.
Related formulas
How to Calculate the Difference Between Two Dates with DATEDIF
RelatedHow to Format Text and Dates with the TEXT Formula
RelatedHow to Use Absolute References ($) So a Formula Copies Down Correctly
From the blogHow to Make Sense of Your Spreadsheet Data Without Being an Analyst
Want a formula written for your exact spreadsheet, not a generic example?
Try the free formula generator