Level 6 · Deadlines and same-week checks with WEEKNUM

Logan’s welcome code is two date questions: when does it run out, and how fast did the customer act on it? Both get simple once you remember that a date is a count of days.

A deadline is a sum

The code is valid for 14 days after signup, and the 14 lives in I1. Because dates are numbers, the expiry is an addition:

E2   =C2+$I$1

Customer 722 signed up on 2024-03-01, so the code expires on 2024-03-15. Lock I1 with $ and fill down; if marketing extends the code to 21 days, one cell changes. The same pattern gives every “date plus N days” rule: a return deadline is the delivery date plus the return window, a payment due date is the invoice date plus 30.

If the result shows as a number like 45366, the cell just lacks a date format. Here column E already has one. To count only working days, WORKDAY does what + does but skips weekends; it isn’t in this practice sheet, so we stick to calendar days.

Which week is it?

WEEKNUM(date) returns the week of the year, 1 to 53. By default a week runs Sunday to Saturday, and week 1 is the one that contains January 1. WEEKNUM(date,2) starts weeks on Monday instead. Pick one system and use it on both sides.

Row	Signup	First order	Days between	Same week?
2	Fri 2024-03-01	Fri 2024-03-01	0	yes
3	Sat 2024-03-02	Sun 2024-03-03	1	no
4	Sun 2024-03-03	Mon 2024-03-04	1	yes

Row 3 is the case that catches people out. One day passed, but Saturday ends a week and Sunday starts the next, so WEEKNUM gives 9 and 10. “The same week” and “within 7 days” are different questions; ask which one the person means.

The same week, in IF and AND

F2   =IF(AND(WEEKNUM(C2)=WEEKNUM(D2),YEAR(C2)=YEAR(D2)),"yes","no")

AND is TRUE only when both tests are. The YEAR test matters because week numbers restart every January: customer 736 signed up in week 11 of 2024 and first ordered in September 2025, and on a sheet with a returning customer who orders in week 11 of the next year, the week test alone would say “yes”. Filled down, 24 of the 36 customers ordered in their signup week.

Common mistakes

• WEEKNUM alone. Week 11 comes around every year. Add the YEAR test.
• Mixing week systems. WEEKNUM(C2) against WEEKNUM(D2,2) compares Sunday weeks with Monday weeks.
• Typing the days into the formula. =C2+14 works until the offer changes. Point at $I$1.
• Comparing timestamps directly. C2=D2 is almost never TRUE: the first order carries a time. Compare the parts you care about, the week here, or INT(D2) for the day.