Service and key dates: practice exercises
EDATE, DATEDIF "YM", anniversaries, notice periods, counting with &. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
New starters have a probation period of the number of months in H1. In C2, work out the date each person's probation ends.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Start date | Probation ends | Probation (months) | 3 | |||
| 2 | Dev | 10/11/2026 | ||||||
| 3 | Ben | 19/07/2026 | ||||||
| 4 | Ella | 31/08/2026 | ||||||
| 5 | Sunil | 03/02/2026 | ||||||
| 6 | Rachel | 23/04/2026 | ||||||
| 7 | Grace | 21/12/2026 | ||||||
| 8 | Pippa | 18/05/2026 | ||||||
| 9 | Nadia | 28/10/2026 | ||||||
| 10 | Femi | 02/07/2026 | ||||||
| 11 | Alex | 18/01/2026 |
How it works
EDATE(date, months) moves a date on by whole months, so =EDATE(B2,6) is six months after B2. It copes with short months: six months after 31 August is 28 February.
DATEDIF(start, end, "Y") gives whole years. "YM" gives the months left over after those whole years, which is how "4 years 7 months" is worked out. To compare with a cell in COUNTIF, join the comparison to it with &: ">="&H1.
=EDATE(B3,$G$2) =DATEDIF(B3,$G$1,"Y")&" years" =EDATE(B3,12*(DATEDIF(B3,$G$1,"Y")+1)) =COUNTIF(B2:B4,"<="&EDATE(G1,-60))
Months on from a date
EDATE(date, months) moves a date on by whole months and copes with short months: six months after 31 August is 28 February.
DATEDIF(start, end, "Y") gives whole years, and "YM" the months left over after them. Join them with & to get "4 years 7 months".
Worked example
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Start date | Probation ends | Service | On | 20/11/2026 | |
| 2 | Amira | 31/08/2026 | Probation months | 6 | |||
| 3 | Ben | 04/04/2022 | |||||
| 4 | Cara | 15/12/2019 |
=EDATE(B2,$G$2)in C2. Six months after 31 August 2026 is 28/02/2027: February has no 31st, so EDATE uses the last day of the month.=DATEDIF(B3,$G$1,"Y")&" years "&DATEDIF(B3,$G$1,"YM")&" months"in D3. Ben started on 4 April 2022: 4 years 7 months by 20 November 2026.=COUNTIF(B2:B4,"<="&EDATE(G1,-60))in D6. Five years' service means starting at least 60 months before G1. Only Cara has: 1.
Anniversaries and notice
- The next work anniversary is the start date moved on by the whole years so far, plus one:
EDATE(start, 12*(years+1)). - Statutory notice is 1 week for each full year, at least 1 and at most 12:
MIN(MAX(years,1),12). MAX sets the floor and MIN the ceiling.
What you'll practise
- New starters have a probation period of the number of months in H1. In C2, work out the date each person's probation ends.
- In C2, show each person's length of service on the date in H1 in the form 4 years 7 months.
- In C2, work out the date of each person's next work anniversary after the date in H1.
- By law, an employer must give at least 1 week's notice to someone with under 2 years' service, then 1 week for each full year, up to 12 weeks. In C2, work out each person's minimum notice. Everyone here has worked at least a month.
- Staff get a long-service award once they reach the number of years in H1. In H2, count how many people qualify. Use H1 in your formula, so the count updates if the policy changes.