Reset Week Number in Google Sheets Using Array or Non-Array Formulas

My project duration is from 01/04/2019 to 31/12/2019. How to start counting week number from 01/04/2019 instead of 01/01/2019? I want to reset week number in Google Sheets for April first week to 1 and continue the counting till the end of the year.

In the above example, the month, which I have specified, may not necessarily be April. It can be a different month.

In a different scenario, I want to reset the week number for every month. Is there an efficient solution to these problems without using a script?

Yes! We can start counting week number 1 from any month and date in Google Sheets using the WEEKNUM function.

Let us begin with a basic example to reset week number in Google Sheets. After that, I can provide you an array formula to reset week number of a range.

Of course, the formula will be different for resetting week count for every month and resetting week count for a specific month.

The function WEEKNUM is the key in my formulas. See the syntax first.

WEEKNUM(date, [type])

You can refer to my How to Utilise Google Sheets Date Functions to learn more about the WEEKNUM function.

How to Reset Week Number for a Specific Month

In cell D3 I have keyed in the date 01/04/2019. It’s in dd/mm/yyyy format as per my Sheets’ locale settings. You may enter the date as per your Google Sheets’ locale settings (File > Spreadsheet settings > General > Locale).

If you use the below WEEKNUM formula, you will get the week count as 14. Because the date 01/04/2019 falls in 14th weeks of the year.

=weeknum(D3)

I want to reset this week number 14 to 1. I will explain to you how to properly reset the week number in any specific month or date in Google Sheets.

You can normally use this non-array formula to reset the week number of a specific month.

Non-Array Formula # 1:

=weeknum(D3)-weeknum($D$3)+1
Reset Week Number for a Specific Month - Non-Array

You can convert the above custom week count formula in cell E3 to an auto-expanding array formula. Why it is necessary?

I have my project start date in cell D3 as 01/04/2019. The project ends on 31/12/2019. So I have a long list of dates in D3:D. In that case, you can convert the above formula to an array formula.

Array Formula # 1:

=ArrayFormula(if(len(D3:D),weeknum(D3:D)-weeknum($D$3)+1,))

Explanation:

  • ArrayFormula – To expand the result (make non-array to an array).
  • LEN – Return output only if D3:D contains values.

Additional Tips – ‘Types’ in Weeknum Count

In the above example, I have considered day week begins on Sunday and ends on Saturday. I have achieved this by omitting the ‘type’ argument in the WEEKNUM function (please refer the syntax).

By default, the ‘type’ is 1 and that means “Sunday – Saturday”. You can use different defined numbers to use different week start and end days.

If you consider your day week beings on Monday and ends on Sunday, then modify the above week number resetting formula as below.

Non-Array Formula # 2:

=weeknum(D3,2)-weeknum($D$3,2)+1

Array Formula # 2:

=ArrayFormula(if(len(D3:D),weeknum(D3:D,2)-weeknum($D$3,2)+1,))
Type 2 Week Reset in Specific Month - Array Formula

I have included the ‘type’ as 2 in the formula. You can refer to this Docs Editors Help to know more about the ‘Type’ available to control the week start and end days.

Reset Week Number for Every Month in Google Sheets

How to reset the week number count to 1 for the beginning of every month?

Non-Array Formula # 3:

=weeknum(D3)-weeknum(date(year(D3),month(D3),1))+1

Compared to the Non-Array Formula # 1, here I have changed cell $D$3 inside the WEEKNUM to date(year(D3),month(D3),1) which would dynamically return the month start date in each row.

Array Formula # 3:

This array formula follows the same logic above.

=ArrayFormula(if(len(D3:D),weeknum(D3:D)-weeknum(date(year(D3:D),month(D3:D),1))+1,))
Reset Week Number for Every Month in Sheets

You can follow this method to reset week number count for every month in Google Sheets. Here also you can use the ‘type’ argument as per Non-Array Formula # 2 and Array Formula # 2.

Starting Week Number Count from a User-Specified Date

Assume your project or any activity start date is 18/07/2019. You want to count the week number from this date and the count should start from 1.

Here are the steps to reset week number based on a user-specified date.

Step 1:

Find the day of the week of the date 18/07/2019 which is in cell D2. We can use the TEXT function for this.

=text(D3,"dddd")

Result: Thursday

Step 2:

Find the ‘Type’ (defined number) of the week starting with Thursday as week start day (Thursday – Wednesday). You can see that it’s 14.

Note: To find this number you can check WEEKNUM function in my Google Sheets Functions Guide.

Step 3:

At this point, we can copy the Non-Array Formula # 1 above and modify it as below.

Non-Array Formula # 4:

=weeknum(D3,14)-weeknum($D$3,14)+1

I have used the ‘type’ 14 in the WEEKNUM function here.

Starting Week Number Count from a Custom Date

Can I convert this formula to an array?

No doubt, you can follow the earlier array formulas especially the Array Formula # 1 here.

Array Formula # 4:

=ArrayFormula(if(len(D3:D),weeknum(D3:D,14)-weeknum($D$3,14)+1,))

Time to conclude this topic of how to reset the week number in Google Sheets. Please let me know if you have any questions/comments. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.