Formulas that know what day it is
TODAY() returns today's date and NOW() returns the date and the current time. Neither takes any arguments, and both update on their own, so a sheet that uses them is always current.
=B2-TODAY(): days left until the date in B2 (negative once it has passed).=IF(B2<TODAY(), "Overdue", "On time"): a status that changes by itself.=NOW()-B2: how long ago B2 was, in days and fractions of a day. Multiply by 24 for hours.
DATEDIF: whole years, months or days
=DATEDIF(start_date, end_date, unit)
| unit | Returns |
|---|---|
"Y" | Complete years, e.g. someone's age |
"M" | Complete months |
"D" | Days |
"YM" | Months left over after the whole years |
"MD" | Days left over after the whole months |
The start date must come first. If it's after the end date, DATEDIF returns #NUM!.