DEV Community

ToolBrewLab
ToolBrewLab

Posted on Edited on

I kept converting dates to MMWR epiweeks by hand. Then I stopped.

If you work with weekly surveillance data, you know the drill. A line list lands in your inbox with three hundred dates in it, and someone needs the MMWR week for every single one before the Friday call. Not the calendar week. The epiweek. The one the CDC uses, where the week starts on a Sunday and week one is the first week with at least four days in January.

I used to do it the slow way. A spreadsheet formula here, a lookup table there, and a quiet prayer that I had not mixed up the year boundary. Because that is where it always goes wrong. A case dated 29 December might belong to week one of the next year, and a case on 2 January might still sit in week 52 of the old one. Get that wrong and your weekly counts shift, your charts lie, and nobody notices until the report is already out the door.

So I stopped converting dates by hand, and I use the Bulk MMWR Epiweek Converter now. You paste a column of dates, one per line, and it hands back the epiweek year and week number for each of them, plus the ISO week and the start and end dates for both systems. That is the whole job. No sign-up, no install, nothing to download. It runs in the browser, so the data never leaves your machine, which matters when the dates belong to real cases.

The conversion itself is simple once you know the rule. The hard part is doing it five hundred times without slipping once. That is a job for a tool, not for concentration. One wrong drag down three hundred rows and a whole week of cases lands in the wrong bucket.

Why the year boundary keeps biting

The MMWR week is the CDC system for disease surveillance, and it runs Sunday to Saturday. Week 1 is the week containing 4 January, which is the first week with at least four days in the new year. That sounds tidy until you meet a real December. A Sunday in late December can open week 1 of the next MMWR year, even though the calendar still says December. A date in early January can still belong to week 52 of the old year. The calendar year and the epiweek year are two different clocks, and they only agree in the middle of the year.

ISO weeks make it worse, because they run Monday to Sunday instead, with week 1 defined as the week containing the first Thursday of the year. Both systems have 52 or 53 weeks a year, but since their weeks start on different days, dates near 1 January often end up with different week numbers, and sometimes even different week-years.

The CDC system exists so that every state, every lab and every hospital counts the same week the same way. When your week numbers match theirs, your numbers line up with the national picture. When they do not, you are arguing about definitions instead of looking at the trend.

A worked example with real dates

The tool has a built-in example, and it is the three dates that trip everyone up. Click Load example and it fills in 30 December 2018, 29 December 2024 and 1 January 2025, all three sitting right on a year boundary.

30 December 2018 was a Sunday. MMWR weeks start on Sunday, so it opens the MMWR week that contains 4 January 2019, which makes it MMWR 2019-W01, running 30 December 2018 to 5 January 2019. ISO weeks start on Monday, so that same Sunday still belongs to the final ISO week of 2018, which is ISO 2018-W52, running 24 to 30 December 2018. One date, two systems, two different week-years.

29 December 2024 was also a Sunday, and the same thing happens. It lands in MMWR 2025-W01, running 29 December 2024 to 4 January 2025, while ISO counts it in 2024-W52, running 23 to 29 December 2024. Then 1 January 2025 falls in week 1 of 2025 under both systems, but the week windows still differ. The MMWR week runs 29 December to 4 January with a Sunday start, while the ISO week runs 30 December to 5 January with a Monday start.

I keep coming back to those three rows whenever someone tells me they convert by hand and it is fine. It is fine until the list crosses New Year, and every surveillance list crosses New Year eventually. The table shows the MMWR week with its start and end, and the ISO week with its start and end, for every row.

The formula route, and why I stopped using it

You can do this with formulas, and the tool page shows them. With a date in cell A2, the ISO week is ISOWEEKNUM(A2) and the ISO year is YEAR(A2-WEEKDAY(A2,2)+4). The MMWR year is YEAR(A2-WEEKDAY(A2,1)+4), the start of MMWR week 1 is DATE(MMWRyear,1,4)-WEEKDAY(DATE(MMWRyear,1,4),1)+1, and the MMWR week number is INT((A2-week1start)/7)+1. Google Sheets has no built-in MMWR function at all.

Those formulas are correct, and I have used them. The trouble is the three hundred rows. You paste the formula, you drag it down, you eyeball a few rows near the boundary, and you tell yourself you checked it. You checked five rows out of three hundred, and the one that matters is always the one you skipped because your eyes were tired.

The converter runs the same logic across the whole column at once, with no formulas to write and no dragging. You paste dates in YYYY-MM-DD or MM/DD/YYYY format, one per line, and the table builds itself as you type. There is no button to hunt for. Copy for Excel or Sheets drops the table straight into your spreadsheet as tab-separated text, and Download CSV saves it as a file. The columns arrive with the date, the MMWR week, the MMWR start and end, and the ISO week with its start and end, so the result pastes back into your sheet in one move.

Who this actually helps

Anyone who files weekly reports to a health department will recognise the Friday call. Anyone cleaning surveillance data in R or Python who wants a quick check against their own code will recognise the boundary problem. And anyone who has ever stared at a date like 2026-01-03 and wondered which week it really belongs to knows exactly what I mean.

The common mistakes are all boundary mistakes. Treating WEEKNUM as an MMWR week is the first one, because Excel's default week numbering is a different system entirely, so the numbers will not match. Assuming 1 January is always week 1 is the second, because dates near the boundary can fall in week 52 or 53 of the previous year, or week 1 of the next. Mixing up Sunday and Monday week starts is the third, and swapping them shifts every boundary by a day. A 53-week year is the fourth trap. An MMWR year has 52 or 53 weeks depending on how the calendar falls, and the converter handles both, so a 53-week year does not break the list.

Questions I get asked

What is an MMWR epiweek and how is it different from an ISO week? It is the CDC epidemiological week: Sunday to Saturday, with week 1 being the first week that has at least four days in the new calendar year. ISO 8601 weeks run Monday to Sunday instead, and week 1 is the one containing the first Thursday. Both systems use 52 or 53 weeks, but they draw the boundaries differently.

Why is 30 December 2018 in MMWR week 1 of 2019 but ISO week 52 of 2018? Because 30 December 2018 was a Sunday. MMWR weeks start on Sunday, so that day opens the MMWR week containing 4 January 2019, which is week 1 of the 2019 MMWR year. ISO weeks start on Monday, so the same Sunday still belongs to the last ISO week of 2018, ISO 2018-W52, running 24 to 30 December.

Can I convert a whole column of dates to epiweeks at once and paste the result back? Yes, that is what it is for. Paste the whole column, one date per line, into the converter, click Copy for Excel or Sheets, and paste into your spreadsheet. The columns arrive as tab-separated text, each row carrying the date, MMWR week, MMWR start and end, and ISO week with start and end.

I have stopped converting dates by hand, and honestly I should have stopped years ago. If your Friday involves a spreadsheet full of dates and a deadline, try the converter and see how much of your afternoon you get back.

Top comments (0)