Due Dates and Days Overdue
Your task: In D2:E2, find each invoice's due date and days overdue, then fill them down.
Full instructions, scenario and goal
Scenario
The accounts-receivable team has six invoices, each with an invoice date and payment terms in days. As of the report date in H1 they need to know when each invoice is (or was) due and how many days late it is. Invoices that are not due yet are not late, so they should show 0 and never a negative number.
Your task
- Look at the invoices in A1:C7 and the report date in H1.
- In D2, calculate the due date: the invoice date plus the payment terms.
- In E2, calculate the number of days overdue as of the report date. An invoice that is not yet due, or is due on the report date, is 0 days overdue.
- Copy D2:E2 and paste them over D3:E7. Select Check Answer.
You are done when
- D2:D7 hold the due dates and E2:E7 the days overdue, all as formulas.
- Invoices that are not due yet show 0 days overdue, never a negative number.
- The formulas use the report date in H1, not TODAY().
What you will practise
Add days to a date, subtract two dates, and keep a result from going below zero.
The idea in 30 seconds
Excel stores a date as a whole number: the number of days since 1 January 1900. That is why dates can be added to and subtracted from. A date plus 30 is the date thirty days later, and one date minus another is the number of days between them. The cell's format decides whether the number is shown as a date or as a plain number.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell D2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
Excel stores a date as a whole number: the number of days since 1 January 1900. That is why dates can be added to and subtracted from. A date plus 30 is the date thirty days later, and one date minus another is the number of days between them. The cell's format decides whether the number is shown as a date or as a plain number.
A difference can go negative: counting days overdue for an invoice that is not due yet gives a minus number. MAX with 0 as one of its values, or an IF that tests the comparison first, keeps the result at 0. Avoid TODAY() in a report that must be reproducible, because its result changes every day; a report date kept in a cell gives the same answer every time the sheet is opened.
Skills you will practise
- Date arithmetic
- MAX
- IF
- Dates as numbers
Common mistakes to avoid
- Negative days overdue
- Subtracting without a floor shows -10 for an invoice due in ten days. Overdue days cannot be negative.
- Counting from the invoice date
- Days overdue starts counting after the due date, not from the day the invoice was issued.
- Using TODAY()
- TODAY() changes every day, so last month's report would show different numbers when reopened.