Course / Dates, Rounding & Money

Date maths: gaps, renewals and month ends

Count the days between dates, jump whole months with EDATE and EOMONTH, and measure length of service with DATEDIF. Intermediate · 5 min · DAYS, EDATE, EOMONTH, DATEDIF

Adding days is easy, but months are awkward: they have 28 to 31 days, so "add 30" drifts. Excel has dedicated functions for calendar maths:

• EDATE(date, months): the same day, a number of months later
• EOMONTH(date, months): the last day of a month
• DATEDIF(start, end, unit): whole years or months between two dates

The sheet holds the start dates of a small studio's team. Service is measured up to the date in B9.

Steps

1. How long was each person's notice period? In D2:D7, subtract the offer date from the start date.

2. Probation lasts six months. In E2:E7, use EDATE to find the date six months after each start date.

3. Payroll runs on the last day of each month. In F2:F7, find each person's first payday with =EOMONTH(C2, 0).

4. How many full years has each person worked here, up to the date in B9? In G2:G7, use DATEDIF with the unit "Y". Lock B9 with dollar signs so it stays put as you fill down.

5. Some benefits start after a set number of months. In H2:H7, count the full months of service with the unit "M".

Remember

• Subtract dates (or use DAYS) to count the days between them.
• EDATE moves by whole months and never skips past a short month's end.
• EOMONTH(date, 0) is the last day of that month.
• DATEDIF(start, end, "Y") counts completed years; "M" counts completed months.

Previous: How Excel stores dates · Next: Working days and due dates