WEEKNUM function

WEEKNUM returns the week number (1–53) of a date within the calendar year, useful for identifying items expiring in the same week.

=WEEKNUM(serial_number, [return_type])

Generate a WEEKNUM formula

Describe what you need. The generator will reach for WEEKNUM where WEEKNUM is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How WEEKNUM reads its arguments
serial_numberrequiredreturn_typeoptionalWEEKNUM →
ArgumentRequiredDescription
serial_numberRequiredRequired. A date value or cell reference; must be a valid date or serial number. Non-dates return #VALUE!.
return_typeOptionalOptional. Defines week numbering: 1=week starts Sunday (default), 2=week starts Monday, 21=ISO 8601 standard (Monday-start).

Returns

An integer from 1 to 53 representing which week of the year the date falls in.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find which week an ingredient expires

IngredientSupplierUnitQtyExpiry Date
Olive OilDiOrioLiter52026-03-15
Sea SaltAtlantic Seakg22027-01-20
GarlicLocal FarmHead242026-09-27
BasilFresh Herbs CoBundle82026-09-20
Balsamic VinegarModena ImportmL7502026-11-10
=WEEKNUM(E3)

Result: 39

WEEKNUM extracts the week number from the Basil expiry date (2026-09-20), which falls in week 39 of 2026 using the default system where weeks begin on Sunday.

2. Compare expiry dates using ISO week numbers

IngredientSupplierUnitQtyExpiry Date
Olive OilDiOrioLiter52026-03-15
Sea SaltAtlantic Seakg22027-01-20
GarlicLocal FarmHead242026-09-27
BasilFresh Herbs CoBundle82026-09-20
Balsamic VinegarModena ImportmL7502026-11-10
=WEEKNUM(E5,21)

Result: 46

Using return_type=21 (ISO 8601 standard with Monday as week start), Balsamic Vinegar's expiry (2026-11-10) falls in week 46. ISO weeks are internationally standardized and useful for cross-border restaurant operations.

3. Identify items expiring in the same week

IngredientSupplierUnitQtyExpiry Date
Olive OilDiOrioLiter52026-03-15
Sea SaltAtlantic Seakg22027-01-20
GarlicLocal FarmHead242026-09-27
BasilFresh Herbs CoBundle82026-09-20
Balsamic VinegarModena ImportmL7502026-11-10
=WEEKNUM(E3)=WEEKNUM(E4)

Result: TRUE

Comparing WEEKNUM(E3) for Basil (week 39) and WEEKNUM(E4) for Garlic (week 39) returns TRUE because both expire in the same week, enabling batch scheduling of reorders or inventory disposals.

Common errors

Which WEEKNUM error are you seeing?
WEEKNUM returned an error#VALUE!
Verify serial_number references a date column. If using text dates, wrap in DATEVALUE(). Ensure return_type is a number: 1, 2, or 21.
#NUM!
Check that the date is within 1900–9999. For return_type, use only 1, 2, or 21. Avoid zero or values outside this set.
#REF!
Restore the deleted column, or update the formula to reference the correct column. Use Undo if the deletion was recent.
ErrorWhy it happensHow to fix it
#VALUE!serial_number contains text that is not a valid date, or return_type is not a recognized number (e.g., a text string instead of 1, 2, or 21).Verify serial_number references a date column. If using text dates, wrap in DATEVALUE(). Ensure return_type is a number: 1, 2, or 21.
#NUM!The date is outside the supported range (before 1900-01-01 or after 9999-12-31), or return_type is an invalid number like 0, 3, or 99.Check that the date is within 1900–9999. For return_type, use only 1, 2, or 21. Avoid zero or values outside this set.
#REF!The cell reference in serial_number (e.g., E3) points to a deleted column or an invalid row after a row or column deletion.Restore the deleted column, or update the formula to reference the correct column. Use Undo if the deletion was recent.

Tips and when to use something else

  • WEEKNUM always counts from January 1 as the start of week 1. Use ISOWEEKNUM for ISO 8601 week numbering if you need the first week of the year treated differently.
  • return_type=21 (ISO 8601) is standard for international business; return_type=1 (Sunday start) is common in the US and aligns with traditional calendars.
  • Combine WEEKNUM with TODAY() to flag ingredients expiring this week, or nest with IF to create priority alerts for critical stock items.
  • Do not use WEEKNUM for calculating elapsed time between dates — use DATEDIF or DAYS for duration. WEEKNUM only tells you which week a date falls into.

Related functions

TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.WEEKDAYWEEKDAY returns the day of the week (1–7) for a given date, with customizable numbering systems (Sunday=1, Monday=1, or ISO 8601).ISOWEEKNUMReturns the ISO 8601 week number (1–53) for a date, where week 1 starts on Monday and contains at least four days in the calendar year.

Frequently asked questions

Why does WEEKNUM return different values when I change return_type?
WEEKNUM counts weeks differently based on where the week starts. return_type=1 (Sunday start) treats Jan 1 as week 1 if it's a Sunday; return_type=21 (ISO) may assign early January dates to the last week of the previous year if they fall before the first Monday.
Does WEEKNUM work the same in Excel and Google Sheets?
Yes, for return_type values 1, 2, and 21. Some versions of Excel support additional codes, but these three are reliable across both platforms. Always test on your target platform.
How do I find which week within the month a date falls into?
WEEKNUM cannot do this because it counts from January 1 each year. Instead, use CEILING(DAY(date)/7) to approximate weeks within a month, or calculate manually with DAY() and ROUNDUP().
What is the difference between WEEKNUM and WEEKDAY?
WEEKNUM returns the week number of the year (1–53), useful for scheduling by week. WEEKDAY returns which day of the week a date is (1–7, typically Sunday–Saturday), useful for day-of-week logic like knowing if it's a Monday or Friday.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17