Description
The WEEKVALUE function converts a text string in epi week format into a week value.
Usage
WEEKVALUE(text)
| Argument | Type | Required | Description |
|---|---|---|---|
| text | text | Yes | A text representation of an epi week, in the form YYYYWww (for example, 2024W05) |
Remarks
ActivityInfo uses epi weeks (also known as MMWR weeks, as defined by the U.S. Centers for Disease Control and Prevention). Each epi week begins on a Sunday and ends on a Saturday. The first epi week of the year is the first week that contains at least four days in the new calendar year.
The expected format is the four-digit year, followed by a capital letter W, followed by the week number (for example, 2015W01 , 2024W30). Week numbers below 10 may be written with or without a leading zero — both 2024W05 and 2024W5 are accepted.
Leading and trailing whitespace in the input is ignored.
The year must be 1900 or later. The week number must be between 1 and 53. A week number of 53 is only valid in years that contain 53 epi weeks (such as 2008, 2014, and 2020); otherwise, the function returns an error.
If the input cannot be parsed as a valid epi week, the function returns an error.
Accepted formats
The following inputs are all parsed correctly:
| Input | Parsed as |
|---|---|
| 2015W01 | Week 1 of 2015 |
| 2024W05 | Week 5 of 2024 |
| 2024W5 | Week 5 of 2024 |
| 2024W30 | Week 30 of 2024 |
| 2020W53 | Week 53 of 2020 |
| 2019W52 | Week 52 of 2019 (whitespace trimmed) |
The following inputs are rejected and produce an error:
| Input | Reason |
|---|---|
| 2019W53 | 2019 has only 52 epi weeks |
| 2024W54 | Week number must be between 1 and 53 |
| 2024W00 | Week number must be at least 1 |
| 1899W01 | Year must be 1900 or later |
| 2024-05 | Missing the W separator |
| 2024w05 | The W must be uppercase |
Examples
Parsing a week imported as text
When importing weekly data from an external source, the reporting week may arrive as text. Use WEEKVALUE to convert it into a week value that can be used in calculations and aggregations:
WEEKVALUE(REPORTING_WEEK)
Converting a fixed week string
WEEKVALUE("2024W05")
returns the epi week beginning Sunday, January 28, 2024.
Building a week from year and week number components
If a form captures the year and week number as separate numeric fields, you can combine them into an epi week using CONCAT :
WEEKVALUE(CONCAT(YEAR_FIELD, "W", WEEK_FIELD))