I have posted several Sheets tutorial on the current week related calculations (most of them linked at the last portion of this tutorial). But none of them touches the current week this year and same week last year’s calculations.

You need one formula to find the dates and another formula to filter the dates that fall in the current week this year/last year.

Assume you have 2-3 years of sales data recorded in Google Sheets and want to find the average or some other calculations based on the current and previous same periods (here weeks). Here come my following tips useful.

All these I am going to explain in detail and also in a precise manner in this Google Sheets tutorial. Let’s begin with the filter formulas.

Formula to Filter Current Week This Year

The Filter is one of the useful functions in Google Sheets for data manipulation. This function is very useful with its unique filtering capabilities like the use of Regex within criteria.

I am not touching that part here. Here let’s pay our attention to how to filter the data for the current week this year and the same week last year.

Filtering the current week this year is pretty easy. Here is the formula.

=filter(A2:C,weeknum(A2:A)=weeknum(today()))

I have already explained/used this formula here – Formula to Sum by Current Week in Google Sheets. Please check that if you face issues in understanding the formula.

Let’ go to the next formula, i.e. the previous same period in filtering dates.

Formula to Filter Current Week Last Year

To filter the current week last year you need not change the above formula! Yes, use it as it is as the above formula not uses years in the calculation, instead uses Week Number.

New to Weeknum and Today functions? Read this guide – How to Utilise Google Sheets Date Functions [Complete Guide].

Since you have learned to filter the data that falls in the current week this year and the same week last year, you can start proceeding with other calculations.

I mean find the Average/Sum/Max/Min etc. of the amount column (third column in the above output) for comparisons.

Find the Dates Fall in Current Week This Year and the Same Week Last Year

Here we don’t have a date column to filter based on the week number. Even though we can use the above formula here! Yes! We are going to use the Filter again for finding dates. Weird, right? Then, how?

Instead of the filter range in the above formula use a virtual range. The virtual range is actually a list of total dates in the current year from 1st January to 31st December.

Let me share with you the virtual range formula that populates dates. There are two steps involved to form the virtual range.

Step 1: Days Formula to Find Total Days in Current Year

The below Days + Today combo formula can find the total number of days in the current year.

=days(date(year(today()),12,31),date(year(today()),1,1))+1

We can use this in a Sequence function to populate a column with dates from Jan to Dec (whole current year). See that details under step 2 below.

Step 2: Sequence Formula to Populate All the Dates in Current Year

I think, I should share the Sequence Syntax first so that I can easily explain the formula to you.

SEQUENCE(rows, [columns], [start], [step])

The above formula replaces the ‘rows’ argument in the Sequence syntax. The ‘columns’ to return is 1 (number of column output) and the start (sequence starting number) is the first date in the year.

Note: Don’t forget that dates are actually recorded as numbers on Sheets. To know that try to format a date to number from the format menu.

=sequence(days(date(year(today()),12,31),date(year(today()),1,1))+1,1,date(year(today()),1,1))

Formula to Find Current Week This Year

Use the above formula as the range in our earlier Filter. I mean replace the range A2:C and A2:A with the just above sequence formula.

The formula to find the dates falls in the current week this year is as follows.

=filter(sequence(days(date(year(today()),12,31),date(year(today()),1,1))+1,1,date(year(today()),1,1)),weeknum(sequence(days(date(year(today()),12,31),date(year(today()),1,1))+1,1,date(year(today()),1,1)))=weeknum(today()))

To find the current week last year, change year(today()) in the formula with year(today())-1.

Formula to Find Current Week Last Year

=filter(sequence(days(date(year(today())-1,12,31),date(year(today())-1,1,1))+1,1,date(year(today())-1,1,1)),weeknum(sequence(days(date(year(today())-1,12,31),date(year(today())-1,1,1))+1,1,date(year(today())-1,1,1)))=weeknum(today()))

That’s all about finding the current week this year and the same week last year in Google Sheets. Thanks for the stay. Enjoy!

Related Reading:

1. Find the Date or Date Range from Week Number in Google Sheets.
2. How to Find Current Month’s Week Number In Google Sheets.
3. Reset Week Number in Google Sheets Using Array or Non-Array Formulas.
4. How to Calculate the Moving Sum of Current Week in Google Sheets.
5. SUMIF Formula to SUM Current Week’s Data in Google Sheets.
6. SUMIF to Sum By Current Work Week in Google Sheets.
7. Query to Sum Current Work Week Range in Google Sheets.