FormuladrillTODAY and NOW
TODAY() returns the current date and NOW() the current date and time, both as a date serial that updates every time the sheet recalculates. Use them for days until a due date (=B2 - TODAY()), overdue flags and age; for a fixed timestamp type Ctrl+; instead.
Syntax
TODAY() NOW()Examples
=TODAY()Result: The current date, formatted as a date
=B2 - TODAY()Result: Days until the due date in B2 (negative when it is past)
=IF(AND(C2 = "Open", B2 < TODAY()), "Overdue", "")Result: Overdue for open items whose due date has passed
=DATEDIF(A2, TODAY(), "y")Result: Age in complete years from the birth date in A2
Common mistakes
- The date shows as a number like 46329: format the cell as a date (or as a date and time for NOW).
- The value changes every day: TODAY is volatile. To freeze a date, press Ctrl+; for a static date or Ctrl+Shift+; for a static time.
- Comparing NOW() with a date: NOW includes a time fraction, so =NOW() = DATE(2026, 11, 3) is FALSE. Use TODAY() or INT(NOW()).
- The workbook asks to save every time you open it: TODAY and NOW recalculate on open and count as changes.
Practice TODAY and NOW
These drills check your answer on live data, free, no account needed.
- Days between datesWork out how many days each task took from start to end. Fill D2 and D3.
Questions
How do I insert today's date in Excel?
=TODAY() for a date that updates daily, or press Ctrl+; for today's date as a fixed value.
How do I calculate days between today and another date?
=B2 - TODAY() for days until B2, or =TODAY() - A2 for days since A2. Format the result as a number, not a date.
How do I get only the time from NOW?
=NOW() - TODAY() or =MOD(NOW(), 1), formatted as h:mm.
Related
- DATE and DATEDIFHow the Excel DATE function builds a date from year, month and day, and DATEDIF counts complete years, months or days between two dates, plus an age formula.
- NETWORKDAYS and WORKDAYHow NETWORKDAYS counts the working days between two dates and WORKDAY adds working days, skipping weekends and holidays, and .INTL for other weekends.
- EOMONTH and EDATEHow the Excel EOMONTH function returns the last day of a month and EDATE adds or subtracts whole months, with examples for payment terms and first of month.
- MODHow the Excel MOD function returns the remainder of a division, with examples for odd and even, every nth row, the decimal part of a number and the sign rule.