# Tenure, quarters & weekdays (Excel lesson 25 of 45)

> Complete years between two dates, which quarter a date falls in, and whether it lands on a weekend.

Source: https://spreadsheetpractice.com/learn/tenure-quarters-weekdays · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Dates"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `DATEDIF`, `WEEKDAY`, `MONTH`, `ROUNDUP`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=DATEDIF(B2,$B$9,"Y")`: Complete years between two dates

## How it works

`DATEDIF(start, end, "Y")` counts complete years between two dates: the right answer for tenure and age. Use `"M"` for complete months and `"D"` for days. Subtracting the dates and dividing by 365 gets leap years wrong.

A calendar quarter is the month divided by three, rounded up: `=ROUNDUP(MONTH(B2)/3,0)`. Join it with text to make a label: `="Q"&ROUNDUP(MONTH(B2)/3,0)`.

`WEEKDAY(date, 2)` numbers Monday as 1 to Sunday as 7, so a value of 6 or 7 is a weekend: `=IF(WEEKDAY(B2,2)>5,"Weekend","")`.

## Syntax

| Formula | What it does |
|---|---|
| `=DATEDIF(B2,$B$9,"Y")` | Complete years between two dates |
| `="Q"&ROUNDUP(MONTH(B2)/3,0)` | Quarter label |
| `=IF(WEEKDAY(B2,2)>5,"Weekend","")` | Flag Saturday and Sunday |
| `=WEEKDAY(B2,2)` | Monday = 1 ... Sunday = 7 |

## Watch out for

- DATEDIF needs the start date first; reversing the dates gives #NUM!.
- WEEKDAY(date) with no second argument counts Sunday as 1. Use 2 for a Monday start.
- Dividing days by 365 drifts by a day every leap year. Use DATEDIF or YEARFRAC.

## The exercises

Solve them in the live grid at [Tenure, quarters & weekdays](https://spreadsheetpractice.com/learn/tenure-quarters-weekdays). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Years of service.** In C2, calculate each employee's complete years of service from the hire date in column B to the "As of" date in B9. Fill down to C7.
2. **Quarter hired.** In D2, show the quarter each person was hired as Q1 to Q4. Fill down to D7.
3. **Weekend starts.** In F2, show Weekend when the hire date falls on a Saturday or Sunday, otherwise leave it blank (""). Treat Monday as day 1. Fill down to F7.

Previous: [EDATE, EOMONTH & NETWORKDAYS](https://spreadsheetpractice.com/learn/edate-eomonth-networkdays) · Next: [UNIQUE & SORT](https://spreadsheetpractice.com/learn/unique-sort)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
