TodayFree forever - no sign-up, no paid plans

How to Calculate Weeks Between Dates in Google Sheets

By Daniel Mercer · Updated November 13, 2026

Open the Weeks Between Dates Calculator

Google Sheets handles dates almost exactly the way Excel does - each date is a day number - so the same INT and MOD pairing gives you full weeks and leftover days. Sheets adds a few wrinkles of its own: dates typed as text, locale settings that swap day and month, and a TODAY() trick that keeps a week count permanently up to date. This guide builds the core formula, then covers workdays, the week-of-year functions, and the fixes for the errors Sheets users hit most.

The core formula: INT for weeks, MOD for days

Put the start date in A1 and the end date in B1. The day count is =B1-A1. Full weeks are =INT((B1-A1)/7) and leftover days are =MOD(B1-A1,7). With January 5, 2026 and March 20, 2026 as the pair, the day count is 74, INT returns 10 weeks and MOD returns 4 days. For one combined label, use =INT((B1-A1)/7)&" weeks, "&MOD(B1-A1,7)&" days". For a decimal answer instead, =ROUND((B1-A1)/7,2) gives 10.57.

Sheets also has DATEDIF, borrowed from Excel: =DATEDIF(A1,B1,"D") returns total days, which you can wrap - =INT(DATEDIF(A1,B1,"D")/7). DATEDIF refuses to run when the end date is earlier than the start date, which is either a useful guard or an annoyance, depending on your data. Plain subtraction with ABS() is more forgiving. Either way, the answers should match what our weeks between dates calculator shows for the same pair of dates - a quick way to validate a new sheet before you share it.

Keeping the count live with TODAY()

The most useful Sheets pattern for week counts is a self-updating one. Put a fixed start date in A1 - a project kickoff, a hire date, the first day of a last period - and use =INT((TODAY()-A1)/7) for completed weeks so far, with =MOD(TODAY()-A1,7) for the days into the current week. The figures refresh whenever the sheet opens. Teams use this for "weeks on project" trackers; individuals use it for countdowns in reverse. Remember that TODAY() follows the spreadsheet's time zone setting (File, Settings), which can shift the count by a day for collaborators in other regions - one more reason the exact day count from a tool like the days between calculator is worth a cross-check on anything important.

Weeks between dates in Google Sheets planned around a moving date

Workdays and custom weekends

For working time, =NETWORKDAYS(A1,B1) counts Monday-to-Friday days including both endpoints, and accepts a holiday range as its third argument. =NETWORKDAYS.INTL(A1,B1,7) uses a Friday-Saturday weekend instead - the "7" is a weekend code, and code 11 gives Sunday only. Two cautions carry over from Excel: NETWORKDAYS counts both the first and the last day when they are workdays, so subtract 1 for an elapsed-workday figure; and dividing workdays by 5 only equals workweeks when the span begins on your week's first workday.

WEEKNUM and ISOWEEKNUM: labels, not rulers

=ISOWEEKNUM(date) returns the ISO week number of a date, from 1 to 53, with weeks starting on Monday. It is the right tool for stamping a row with its week number - for example, grouping sales or hours by week. It is the wrong tool for measuring a gap: subtracting ISOWEEKNUM(B1) minus ISOWEEKNUM(A1) fails across New Year, because week numbers restart, and it ignores partial weeks at both ends. A span from ISO week 47 to ISO week 2 of the next year is 8 weeks, not negative 45. Measure with the day count; label with ISOWEEKNUM.

Counting weeks for a whole list of date pairs

When a sheet holds dozens of spans - course lengths, tenancy periods, project phases - you do not want to copy a formula row by row and guard every blank. Sheets offers two clean patterns. The simple one uses the DAYS function, =DAYS(B1,A1), which returns the same day count as subtraction but reads better and ignores any stray times in the cells. The scalable one is ARRAYFORMULA: a single cell at the top of the weeks column, =ARRAYFORMULA(IF(A2:A="","",INT((B2:B-A2:A)/7))), fills every row automatically, including rows added later, and leaves blanks blank. Pair it with the matching MOD version in the next column and the whole table stays live. One caution: open-ended ranges like A2:A make the sheet recalculate across thousands of empty rows, which can slow very large files - cap the range at a realistic size, such as A2:A500, if the sheet starts to lag. And if the list mixes date orders, wrap the difference in ABS() inside the INT so a reversed pair reports its true length instead of a negative surprise.

Errors Sheets users hit, and the fixes

Dates stored as text. If A1 holds text that merely looks like a date, B1-A1 returns #VALUE!. Test with =ISDATE(A1); if it returns FALSE, re-enter the date or convert with DATEVALUE. Day and month swapped. Sheets follows the file's locale: in a US-locale file 03/04/2026 is March 4; in a UK-locale file it is 3 April. Mixed-locale teams produce week counts that are off by weeks. Enter dates in ISO format (2026-03-04) to remove all doubt. Results shown as dates. A result of 10 formatted as a date displays as January 10, 1900. Set Format, Number to a plain number format. Negative spans. Subtraction handles reversed dates by going negative, and INT on a negative decimal rounds away from zero in a way that surprises; use ABS() around the difference if order should not matter.

Flagging long spans automatically

Once the weeks column exists, a little formatting turns the sheet into an early-warning system. Select the weeks column and add a conditional format rule - Format, Conditional formatting - that colors any cell greater than 12, and suddenly every span longer than a quarter stands out in a list of hundreds. The same trick works on the leftover-days column: highlight leftovers of 5 or 6, because those spans sit just short of another full week and are the ones people most often misquote as the next whole week. If the sheet feeds a report, add a summary cell with =AVERAGE(D2:D) over the decimal-weeks column for the typical span, and =MAX(D2:D) for the longest. Averages belong in decimal weeks - averaging the split form means averaging weeks and days separately and recombining, a small chore that produces a wrong answer often enough that the decimal column earns its place in the sheet even if you never show it to anyone. A sheet maintained this way answers most week questions at a glance, while the exact formulas stay underneath for the day a figure is challenged and someone needs to show the working.

Frequently asked questions

What is the Google Sheets formula for weeks between two dates?

=INT((B1-A1)/7) for full weeks and =MOD(B1-A1,7) for leftover days, with the start date in A1 and the end date in B1. For a decimal answer, use =ROUND((B1-A1)/7,2).

Does Google Sheets have a WEEKS function?

No dedicated weeks-between function exists. Sheets offers WEEKNUM and ISOWEEKNUM, which return the week of the year a date falls in - useful labels, but subtracting them does not measure a span. Build the answer from the day count instead.

How do I count workdays between dates in Google Sheets?

Use NETWORKDAYS(A1,B1), which excludes Saturdays and Sundays, and add a holiday range as the third argument to remove those too. NETWORKDAYS.INTL lets you choose a different weekend, such as Friday and Saturday.

Why does my Sheets formula show a date from 1899 or 1900?

The result cell is formatted as a date or duration. Sheets stores dates as day numbers starting at the end of 1899, so a small number displays as an early date. Set the cell format to Number via Format, Number, Automatic or plain text.

Can I make the formula update to today automatically?

Yes. Replace the end date with TODAY(), as in =INT((TODAY()-A1)/7), and the count refreshes every day. That is the standard way to track weeks since a start date, such as weeks on a project or weeks of a pregnancy.

Related guides

How to calculate weeks between dates in Excel

INT and MOD formulas for full weeks, leftovers and decimals.

How to calculate weeks between two dates

The three-step method: count the days, divide by 7, split the remainder.

How to convert days into weeks and leftover days

One division turns any day count into weeks and leftover days.

ISO week numbers explained

What week of the year it is, and why some years have 53 weeks.

Daniel Mercer

Daniel Mercer builds and maintains the calculators on this site. He spent ten years in payroll and operations roles counting business days, notice periods and deadlines by hand, and writes these guides the way he would explain the count to a colleague - the rule first, a worked example second, and the common mistake called out before it happens.