Calculate Age in Weeks in Excel with a Simple Formula

To calculate completed age in weeks in Excel, put a birth date in A2 and enter =INT((TODAY()-A2)/7) in another cell. Excel subtracts the birth date from today, divides the elapsed days by seven, and returns the number of full weeks. You will need a real Excel date in the birth-date cell; the result updates as the current date changes.

This method reports completed weeks, not a rounded estimate. For a result tied to a particular date instead of today, use a separate as-of date cell. That is useful for reports, records, and examples you want to keep consistent when the workbook is opened later.

What You’ll Need

Excel for Microsoft 365 on Windows, or a recent Excel version that supports the standard date functions used here.
A birth date entered in a worksheet cell. It must be stored as an Excel date, rather than as text that merely looks like a date.
Optionally, a second date cell for the date you want to calculate age as of.

Step-by-Step

Enter the birth date. Put a person’s date of birth in a cell such as A2. Enter it in a date format Excel recognizes for your computer’s regional settings. For example, Excel may interpret 4/5/2020 as April 5 or May 4 depending on those settings, so a less ambiguous entry such as 5-Apr-2020 can help. To check or change how a date is displayed, select the cell and choose Home > Number format dropdown > Short Date. Changing the display format does not repair a value stored as text, but it can make a valid date easier to inspect.
Choose whether the result should use today or a set date. For a live age calculation, use Excel’s TODAY function in the formula in the next step. For a calculation that must stay fixed to a reporting date, enter that date in B2. For example, type 2-Nov-2026 in B2 and use it as the as-of date. Keep the birth date in A2 and the as-of date in B2 so the formula can compare the two cells.
Calculate completed weeks as of today. With the birth date in A2, select C2 or another empty result cell and enter =INT((TODAY()-A2)/7). Press Enter. The formula subtracts A2 from today’s date, divides the elapsed days by seven, and uses INT to discard any incomplete week. For instance, if A2 is 1-Jan-2024 and today is 2-Nov-2026, the elapsed time is 1,036 days; 1,036 divided by 7 is exactly 148, so the formula returns 148. TODAY changes when Excel recalculates the workbook, so the number can increase over time.
Calculate completed weeks as of a chosen date instead. If A2 contains 1-Jan-2024 and B2 contains 2-Nov-2026, put this formula in C2: =INT((B2-A2)/7). It returns 148 because the two dates are 1,036 days apart, or 148 complete seven-day periods. This version is preferable when you need to reproduce an age for a past or future reporting date. To change the result, edit B2 rather than rewriting the formula.
Decide whether you need full weeks or a fractional value. The INT formulas above count only complete weeks. If A2 contains a birth date and B2 contains an as-of date, use this formula in C2 when you want weeks including a decimal fraction: =(B2-A2)/7. For the dates in the previous example, it returns 148. The remainder is zero because the elapsed days divide evenly by seven. If the dates are not an exact number of weeks apart, the result includes a decimal, such as 148.4. You can show fewer decimals by selecting the result cell and using Home > Number to decrease the displayed decimal places; this changes the display, not the underlying value.
Copy the formula down for a list. If birth dates are in A2:A20, enter the appropriate formula in C2 and use the fill handle at the lower-right corner of the cell to copy it down through the rows. For a fixed-date calculation, keep the as-of date in B2 and adjust the layout so each row has its own birth date and refers to the correct as-of date cell. One simple arrangement is birth dates in A2:A20, the same as-of date in B1, and results in C2:C20. With that layout, use =INT(($B$1-A2)/7) in C2, then fill down; the dollar signs keep the shared date fixed while the birth-date reference changes by row. If B1 is 2-Nov-2026 and A2 is 1-Jan-2024, C2 returns 148.

For a live list, the arrangement can instead have birth dates in A2:A20 and a separate result in B2:B20. With the birth date in A2, enter =INT((TODAY()-A2)/7) in B2 and fill down. Each row will use the date in its own column A. Check that the formula references the intended row after copying it, especially if you have inserted headings or blank lines.

Common Mistakes

Using a text value instead of a date. A cell can display something date-like while Excel stores it as text. Date subtraction may then return #VALUE!. Re-enter the date in a recognized format, or use Text to Columns to convert imported date text when appropriate. Do not assume that applying Short Date formatting alone converts text into a real date.
Rounding when you mean completed weeks. INT returns the number of full weeks by rounding a positive result down. ROUND, ROUNDUP, or simply displaying a decimal can answer a different question. Someone who is 10 days old has one completed week, not two, even though 10 divided by 7 is about 1.43.
Mixing up the date order. Excel date subtraction is end date minus start date. Reversing the references can produce a negative result. For a birth date in A2 and an as-of date in B2, subtract A2 from B2, not B2 from A2.
Expecting a fixed result from TODAY. TODAY is dynamic. A workbook opened or recalculated on a later day will use the later date. Use a date entered in a cell for an audit, saved snapshot, or historical report that must remain reproducible.
Including time values without realizing it. Excel can store a time alongside a date. If the birth value includes a time, the calculation uses that fraction of a day too. For ordinary date-of-birth records, store date-only values so a time does not affect the elapsed interval.

Tips

Label the answer clearly. Add a heading such as “Completed age (weeks)” above the result column. This distinguishes an integer count from a decimal age in weeks and helps other people understand what the number means.
Keep a fixed as-of date in one cell. A single report date makes every row use the same cutoff. In the example with dates in A2:A20, put the cutoff in B1 and use the absolute reference $B$1 in the formula before filling it down.
Guard against blank birth-date cells. If you are filling a live formula down a column that may have empty rows, use this formula with a birth date in A2 and the result in B2: =IF(A2="","",INT((TODAY()-A2)/7)). It leaves the result blank when A2 is blank, instead of showing a number based on an empty cell. Keep the quotation marks in the formula as shown.
Prevent future birth dates from looking like valid ages. With the birth date in A2 and a live calculation in B2, use =IF(A2>TODAY(),"Check date",INT((TODAY()-A2)/7)). This displays a warning for a date after today and otherwise returns completed weeks. The result column will contain text for the warning and numbers for valid dates, which is usually fine for a simple list but may matter if you later calculate statistics over that column.
Keep dates unambiguous when sharing a workbook. A date such as 03/04/2026 can be interpreted differently across regional settings. Use a clearly written date when entering values, and consider applying a consistent display format for the workbook so readers can tell which day and month are intended.

Troubleshooting

The formula returns #VALUE!. Check that the birth date and any as-of date are stored as dates rather than text. Select each cell and look at the formula bar, then try changing its number format to Short Date. If the value does not behave like a date after formatting, re-enter it or convert the imported text. Also check that the formula contains cell references and valid date values, not an accidentally pasted label.

The answer is negative. The as-of date is earlier than the birth date, or the formula has its dates in the wrong order. Confirm the values in both cells and use end date minus birth date. If the date is genuinely in the future, correct it or use the warning formula from the Tips section.

The result shows a date instead of a number. Excel may have applied a date number format to the result cell. Select the result and choose Home > Number format dropdown > General or Number. The formula result is a count of weeks; formatting determines how that stored number appears.

The result does not update. TODAY recalculates when Excel recalculates the workbook. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. If calculation is set to Manual, change it to Automatic or recalculate the workbook. If the result is based on a fixed as-of date cell, update that cell yourself; the formula will not replace a date you entered.

Copied formulas all use the same birth-date row or the wrong cutoff. Inspect the references in a few rows. In the fixed-date example, the birth-date reference should change from A2 to A3 as you fill down, while the shared as-of date remains $B$1. If the result column shows unexpected values, confirm the dollar signs are applied only to the reference that must stay fixed.

The number is one week lower than expected. INT counts full seven-day intervals, not calendar week labels or rounded weeks. Check the exact dates and count the elapsed days. If you want a decimal age, use the division-only formula; if you want the nearest whole week rather than completed weeks, choose an explicit rounding method and understand that it answers a different question.

Frequently Asked Questions

Does Excel have a built-in AGE IN WEEKS function?
No. A simple date subtraction divided by seven is the practical approach. Wrap the result in INT when the goal is to count completed weeks.

Does this calculate a person’s age in calendar weeks?
No. It calculates elapsed seven-day periods since the birth date. Calendar week numbers depend on the calendar and week-start convention, so they are a different measurement.

Should I use TODAY or a date in a cell?
Use TODAY for an age that should stay current. Use a date entered in a cell when the calculation needs a consistent cutoff, such as a report date or a historical record you may review later.

Can I show weeks and remaining days?
Yes. Calculate completed weeks with INT, then calculate the remaining days from the elapsed day count after subtracting those full weeks. For an easy-to-read report, you can also show total days and completed weeks in separate columns. Make sure the as-of date and birth date are the same pair of dates in both calculations.

Why does my decimal result show many digits?
Division can produce a repeating decimal. Use the number-format controls to display a chosen number of decimal places, or retain the INT formula if only completed weeks are meaningful. Formatting changes what you see, not the calculation’s precision.

Will the instructions work on Mac or Excel for the web?
The formulas and date arithmetic work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon layouts can differ slightly, but the relevant number-format controls are available. On Mac, Command+1 opens Format Cells; on Windows, Ctrl+1 opens it.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.