SheetsdrillTODAY and NOW
TODAY() returns the current date and NOW() the current date and time, both with no arguments and both recalculated whenever the sheet changes. Subtracting dates gives whole days, so =B2-TODAY() is the number of days until the date in B2.
Syntax
=TODAY() and =NOW()Examples
=TODAY()Result: The current date, for example 11/3/2026
=B2-TODAY()Result: Days until the due date in B2 (negative when it has passed)
Format the result as a number, not a date.
=IF(AND(B2<TODAY(), C2<>"Paid"), "Overdue", "")Result: Overdue for unpaid invoices whose due date has passed
Common mistakes
- The subtraction shows a date like 1/23/1900 instead of a number; change the cell format to Number.
- NOW() includes a time, so NOW()-B2 has a fraction; use TODAY() or INT(NOW()) for whole days.
- TODAY() changes every day, so a timestamp entered with it moves; type the date or use Ctrl+; for a fixed date.
Practice TODAY and NOW
These drills check your answer on live data, free, no account needed.
- Full years of serviceWork out each employee's completed years of service on the report date in F1. Fill C2 and C3 using DATEDIF.
Questions
How do I insert a date that does not change?
Press Ctrl+; for the date and Ctrl+Shift+; for the time, or type it.
How do I calculate age from a birth date?
=DATEDIF(B2, TODAY(), "Y").
Related
- DATEDIFHow DATEDIF in Google Sheets returns whole years, months or days between two dates, with the unit codes, examples for age and tenure, and common errors.
- NETWORKDAYSHow NETWORKDAYS and NETWORKDAYS.INTL in Google Sheets count working days between two dates, exclude holidays, use custom weekends, and add days with WORKDAY.
- DATE, YEAR, MONTH and DAYHow DATE, YEAR, MONTH and DAY work in Google Sheets to build a date from parts, pull the year or month out of a date, group by month and fix text dates.
- IFHow the IF function in Google Sheets returns one value when a condition is true and another when it is false, with examples for text, blanks and nested IFs.