Excel track · Exercise 74 of 86 Paid
Date functions summary
Combine today's date, a distance and a month edge in formulas that still work tomorrow.
Intermediate
A policy register and an invoice payment log
Read-only data · screen 1 of 2 · answer in C2
The workbook is pinned to 15 March 2024, so put the record's age in whole months into C2.
| A | B | C | |
|---|---|---|---|
| 1 | Record | Opened | Age (months) |
| 2 | POL-1041 | 44562 | |
| 3 | POL-1042 | 44927 |
Pay on the last day of the month after the invoice date, so fill C2 with a formula rather than a date.
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Issued | Payment date |
| 2 | INV-1 | 45352 | |
| 3 | INV-2 | 45383 |
Reference answer
What it does
Three functions cover most date work. TODAY returns the current day, DATEDIF measures a distance in whole units, and EOMONTH lands on the last day of a month. Each returns a serial, so each can feed the next formula.
The first screen ages a policy opened in January 2022 against a clock pinned to 15 March 2024, and asks for whole months: DATEDIF stops at 26. The second pays an invoice dated 1 March on the last day of the following month, which is 30 April, serial 45412. EOMONTH's second argument is a month offset, not a unit.
Keep the offset and the unit apart: EOMONTH's 1 moves one month forward and DATEDIF's "M" counts whole months. A wrong offset still returns a date and a wrong unit still returns a number, so compare the answer with the calendar.
Syntax
=EOMONTH(start, months) with =DATEDIF(start, end, unit)Worked example
On A policy register and an invoice payment log, =DATEDIF(B2,TODAY(),"M") returns 26.
=DATEDIF(B2,TODAY(),"M")Common errors
| Error | What it means and how to fix it |
|---|---|
#NUM! | DATEDIF was given an end date before its start date, which leaves no positive interval to count. |
#NUM! | EOMONTH was asked for an offset that lands outside the range of serials, such as a date before 1900. |
#VALUE! | A date argument arrived as text that cannot be read as a date, which leaves no serial underneath it. |
At work
A policy register aged against today, and an invoice log that pays on the last day of the following month.