Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
DATEDIF. DATEDIF(start, end, unit) counts complete units between two dates: "y" whole years, "m" whole months, "d" days, and "ym" the months left over after the whole years. The start must not be after the end. Excel doesn’t list it in its function menu, but it works.
=DATEDIF(B2,$H$1,"m") whole months a customer has been signed up
The long-service awards go to reps by completed years at Brightline as of the year-end date in B1. HR also wants the extra months, to write tenure like 5 years 10 months.
In C4:C11, return each rep’s completed years from the hire date in column B to the date in B1. In D4:D11, return the completed months left over after those years. Point at B1.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Year end | 2025-12-31 | ||||||
| 2 | ||||||||
| 3 | Rep | Hire date | Years | Extra months | ||||
| 4 | Grace Whitfield | 2016-04-04 | ||||||
| 5 | Elena Petrova | 2018-01-16 | ||||||
| 6 | Marcus Hill | 2018-10-07 | ||||||
| 7 | Priya Raman | 2017-11-06 | ||||||
| 8 | Daniel Ortiz | 2017-05-27 | ||||||
| 9 | Ivan Kolev | 2019-10-02 | ||||||
| 10 | Hannah Cole | 2019-07-02 | ||||||
| 11 | Ben Carter | 2021-09-18 | ||||||
| 12 | ||||||||
| 13 | ||||||||
| 14 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe