TODAY: a moving date in a live workbook
TODAY changes with the day the workbook recalculates. That is convenient for rolling deadlines, but it can make a historical report drift over time.
The strict < comparison excludes tasks due on the report date. After checking the fixed-date example, replace E2 with TODAY() if the report is intended to roll forward automatically. Keeping the date in one visible cell makes the report assumption easy to inspect.
| Formula | Expected result / interpretation |
|---|---|
| =IF(AND(A2<$E$2,B2<>”Done”),”Overdue”,”OK”) | Overdue; fill down for OK, OK |
| =TODAY() | The current date at workbook recalculation, not a fixed historical date |
| Row | A: Due date | B: Status |
|---|---|---|
| 2 | 2026-09-30 | Open |
| 3 | 2026-10-01 | Open |
| 4 | 2026-09-29 | Done |
For a reproducible exercise, enter =DATE(2026,10,1) in E2 as the report date. Enter due dates in A2:A4 with DATE and statuses in B2:B4.
Flag overdue tasks while keeping reports reproducible
Formula syntax
=TODAY()
A quick example first
=TODAY()
Returns the current date according to the spreadsheet environment.
=TODAY()-A2
Returns elapsed days when A2 is a valid earlier date.
Before you copy this into a real sheet
A TODAY formula is not a permanent timestamp and does not continuously tick without recalculation. A report reopened on another day may change. For audit snapshots store the approved report date as a value, and document the relevant timezone for shared operational reports. Missing or text due dates need separate input validation; this short rule assumes valid date cells. Use NOW only if the decision requires a time-of-day threshold rather than a whole-day comparison.
Useful habits
- TODAY is volatile in the sense that it changes as the date changes/recalculates.
- Store fixed reporting dates in cells when you need a reproducible historical snapshot.
- Use NOW when time-of-day is also required.
Where people get caught out
- Expecting TODAY to remain fixed after the workbook is reopened.
- Subtracting a text-formatted date that was never converted to a real date value.
Other functions worth a look
A detail worth remembering
If you need a permanent record of the review date, use a fixed date value rather than a recalculating TODAY formula.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.