Lesson 25 of 45, Dates
Tenure, quarters & weekdays
Quick answer
Complete years between two dates, which quarter a date falls in, and whether it lands on a weekend.
- Key formula
=DATEDIF(B2,$B$9,"Y")(Complete years between two dates)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
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
=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 = 7Watch 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
- 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.
- Quarter hired. In D2, show the quarter each person was hired as Q1 to Q4. Fill down to D7.
- 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.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Years of service
- Quarter hired
- Weekend starts