Date & Time

Calculate Days Open & Invoice Age Status (Open vs Closed Items)

Calculate elapsed calendar days as of an explicit report date, cap aging at a valid closure date, and label active items using a configurable age threshold.

Calculate age as of a report date

B2 is the opening date, C2 is an optional closing date, E2 is the report date and E3 is a nonnegative whole-day threshold. Dates in this example use DATE formulas so their values are numeric date serials rather than locale-dependent text.

F2 calculates elapsed calendar days. A closure on or before the report date stops aging at closure; a closure after the report date leaves the item open as of that report. An item opened after the report date is marked Not yet open. Reversed or invalid dates produce a diagnostic message.

The status formula in G2 uses F2. Exactly 30 days is Current for a threshold of 30; Overdue begins at 31. This is an illustrative age policy, not an invoice due-date calculation or a business-hours SLA. Closed describes state, not whether the item met its deadline.

COUNTBLANK treats an empty cell or a formula returning empty text as no closure. ISBLANK alone does not treat an empty-text formula as an empty cell. INT removes time-of-day fractions, so this measures calendar-date differences, not complete 24-hour intervals.

Elapsed calendar days in F2

Enter this formula in F2. An invalid date remains visible as a message instead of silently becoming a zero-date.

=IF(COUNT(B2,$E$2)<>2,"Enter valid date serials",IF(OR(B2<1,$E$2<1),"Enter valid date serials",IF(COUNTBLANK(C2)=0,IF(COUNT(C2)<>1,"Enter valid close date",IF(INT(C2)<INT(B2),"Close precedes open",IF(INT($E$2)<INT(B2),"Not yet open",MIN(INT(C2),INT($E$2))-INT(B2)))),IF(INT($E$2)<INT(B2),"Not yet open",INT($E$2)-INT(B2)))))

Status as of the report date in G2

Enter this formula in G2 after F2. Copy both formulas down to age additional items while keeping the report controls fixed.

=IF(NOT(ISNUMBER(F2)),F2,IF(COUNT($E$3)<>1,"Enter a nonnegative whole-day limit",IF(OR($E$3<0,$E$3<>INT($E$3)),"Enter a nonnegative whole-day limit",IF(COUNTBLANK(C2)=0,IF(INT(C2)<=INT($E$2),"Closed",IF(F2>$E$3,"Overdue","Current")),IF(F2>$E$3,"Overdue","Current")))))

Try the example

Use the original values or change them to test your understanding.