Confident Cells

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.

Your turn

New starters have a probation period of the number of months in H1. In C2, work out the date each person's probation ends.

ABCDEFGH
1EmployeeStart dateProbation endsProbation (months)3
2Dev10/11/2026
3Ben19/07/2026
4Ella31/08/2026
5Sunil03/02/2026
6Rachel23/04/2026
7Grace21/12/2026
8Pippa18/05/2026
9Nadia28/10/2026
10Femi02/07/2026
11Alex18/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

ABCDEFG
1NameStart dateProbation endsServiceOn20/11/2026
2Amira31/08/2026Probation months6
3Ben04/04/2022
4Cara15/12/2019
  1. =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.
  2. =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.
  3. =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

  1. New starters have a probation period of the number of months in H1. In C2, work out the date each person's probation ends.
  2. In C2, show each person's length of service on the date in H1 in the form 4 years 7 months.
  3. In C2, work out the date of each person's next work anniversary after the date in H1.
  4. 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.
  5. 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.