Weekdays, renewals and reporting periods

The first dates lecture was arithmetic. This one covers the calendar questions that come up in every ops and finance sheet: which day of the week, when something renews, and which month or quarter a row belongs to.

WEEKDAY(date, 2) returns the day of the week as a number, Monday 1 through Sunday 7. Always pass the 2. Without it, the count starts on Sunday as 1, and a test like >5 for weekends misses every Sunday. With type 2, =IF(WEEKDAY(B2, 2)>5, "Weekend", "Weekday") is all it takes.

EDATE(date, months) moves by calendar months and keeps the day: a contract starting August 14 with a 6-month term renews February 14. Adding 180 days lands on February 10, because months aren't all 30 days. A negative number goes back. When the target month is shorter, EDATE uses its last day, so January 31 plus one month is February 28.

DATEDIF(start, end, unit) counts whole units between two dates: "M" for months, "Y" for years, "D" for days. It only counts complete units, so January 15 to October 14 is 8 months, not 9. That's what you want for tenure or customer age. The start date has to come first, or you get #NUM!.

Month and quarter columns. Reports roll up by period, so give the data a column for it, then SUMIFS on that column:

• Month: =TEXT(A2, "yyyy-mm") gives 2026-08. Put the year first so the keys sort in order. If you'd rather have a real date, =EOMONTH(A2, -1)+1 is the first of the month.
• Quarter: =YEAR(A2)&"-Q"&ROUNDUP(MONTH(A2)/3, 0) gives 2026-Q3. MONTH returns 1 to 12, and dividing by 3 and rounding up turns January to March into 1, April to June into 2, and so on.
• Fiscal year starting in February? Shift the date back a month first: MONTH(EDATE(A2, -1)). Now February counts as month 1.

Then =SUMIFS(C:C, D:D, "2026-Q3") totals a quarter, and the same formula works for any period you type in.