TODAY

For the year-end audit of Veldhuis Installatie BV you sent three bank confirmation requests. Your follow-up list needs a column with the number of days each request has been outstanding, and it must be correct every morning without anyone updating it. The example assumes today is 30 September 2026.

Formula in C2

=TODAY() - B2

=TODAY() - B2

Result: 1616

Watch out: If C2 shows 16-1-1900 instead of 16, the cell carries a date format (Excel sometimes applies one to formulas that refer to dates). Set the cell to General or Number.

You are preparing the receivables aging analysis for a client and have the open invoices from the receivables ledger with their due dates. You need a column with the number of days each invoice is overdue, as the basis for the 30, 60 and 90-day bands.

Invoices that are not due yet, or are due today, must show 0 rather than a negative number. The example assumes today is 30 September 2026.

Formula in D2

=IF(TODAY() > C2, TODAY() - C2, 0)

=IF(TODAY() > C2; TODAY() - C2; 0)

Result: 1515

Watch out: An empty due date is read as 0, so the test is TRUE and the row shows 46295 days overdue. This happens when the ledger export has invoices without a due date. Filter those rows out, or test for a blank first.

=MAX(0, TODAY() - C2)

=MAX(0; TODAY() - C2)

The client's loan register lists each loan with its maturity date. For the going concern and classification work you want a live list of the loans that mature between today and the number of days in F1, so you can check the repayment plan and the split between current and non-current liabilities.

Loans that have already matured belong on a different list. The example assumes today is 30 September 2026.

Formula in F3

=FILTER(A2:A7, (C2:C7 >= TODAY()) * (C2:C7 <= TODAY() + F1), "None")

=FILTER(A2:A7; (C2:C7 >= TODAY()) * (C2:C7 <= TODAY() + F1); "None")

Result: LN-01 / LN-04 / LN-06LN-01 / LN-04 / LN-06

Watch out: Writing > TODAY() instead of >= TODAY() silently drops the loan that matures today, LN-06, which is the one most urgently in need of attention.

=COUNTIFS(C2:C7, ">=" & TODAY(), C2:C7, "<=" & TODAY() + F1)

=COUNTIFS(C2:C7; ">=" & TODAY(); C2:C7; "<=" & TODAY() + F1)

You are recalculating depreciation from the fixed asset register and first need the number of completed years since each asset was bought. Subtracting years gives one year too many for every asset whose anniversary is later this year, and the count must be right on the anniversary itself. The formula should call TODAY once and read clearly for the next person on the file. The example assumes today is 30 September 2026.

Formula in C2

=LET(
  asOf, TODAY(),
  bought, B2,
  years, YEAR(asOf) - YEAR(bought),
  reached, DATE(YEAR(asOf), MONTH(bought), DAY(bought)) <= asOf,
  IF(reached, years, years - 1)
)

=LET(
  asOf; TODAY();
  bought; B2;
  years; YEAR(asOf) - YEAR(bought);
  reached; DATE(YEAR(asOf); MONTH(bought); DAY(bought)) <= asOf;
  IF(reached; years; years - 1)
)

Result: 66

Watch out: The mirror-image mistake is testing reached with < instead of <=. It looks the same, but on the anniversary itself the forklift shows 6 instead of 7, exactly on the day the count changes.

=YEAR(TODAY()) - YEAR(B2) - (DATE(YEAR(TODAY()), MONTH(B2), DAY(B2)) > TODAY())

=YEAR(TODAY()) - YEAR(B2) - (DATE(YEAR(TODAY()); MONTH(B2); DAY(B2)) > TODAY())