Summarize Data by Week Start and End Dates in Google Sheets

GiGiKing·March 20, 2024·0 comments

Are you tired of using week numbers to summarize data in Google Sheets? Well, you’re in luck! I have an exciting new concept for you that will revolutionize the way you summarize data. Instead of using week numbers, why not summarize data by week start and end dates? Trust me, you won’t find this type of data summation in any other spreadsheet application.

Now, you might be wondering, “How do I do this?” Don’t worry, I’ve got you covered. While most people rely on the WEEKNUM function for weekly summaries, I’m going to show you a different approach using the WEEKDAY function.

But before we dive into the details, let me quickly share two of my WEEKNUM based tutorials for learning how to sum by week:

  1. How to Create A Weekly Summary Report in Google Sheets
  2. Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets

Now, let’s get back to summarizing data by week start and end dates in Google Sheets. It’s actually quite simple once you know how to convert a date to the corresponding calendar week. Allow me to guide you through the process step-by-step:

WEEKDAY Logic

Let’s take the date 01-Aug-2020 as an example. By using the WEEKDAY function, we can determine that this date falls within the week starting from 27-Jul-2020 to 02-Aug-2020. Armed with this knowledge, preparing a summary report becomes a breeze.

Please follow my step-by-step instructions below:

Example to Summarize Data by Week Start and End Dates - All the Weeks
image # 1

Formula to Summarize Data by Week Start and End Dates in Google Sheets

There are only two major steps involved: using the WEEKDAY and QUERY functions.

Note:

  1. For sample data, please refer to the array A2:B20 in the image above.
  2. Whenever a formula returns date values instead of dates or dates instead of numbers, you can format the range back to the correct format from the format menu (Format > Number > Date or Format > Number > Number).

Data Preparation for the Summary Using WEEKDAY

We will first use two helper columns, C and D (arrays C2:C20 and D2:D20), for data preparation. Later, we can remove these two arrays.

In cell C2, enter the following WEEKDAY based formula:

=ArrayFormula( A2:A20- WEEKDAY(A2:A20,2)+1 )

Here is one more Weekday formula that should be entered in cell D2:

=ArrayFormula( A2:A20- WEEKDAY(A2:A20,2)+7 )

What do these two formulas do?

The formulas check the dates in A2:A20 and return the corresponding week starting dates in the array C2:C20 and week ending dates in the array D2:D20.

image # 2

Formula Explanation

To understand the formula, let’s start with the syntax of the WEEKDAY function:

WEEKDAY(date, [type])

The WEEKDAY function returns the number representing the day of the week of the ‘date’ argument. In cell A2, the date is 01-Aug-2020, and the day of the week for this date is Saturday.

If the week starts from Monday to Sunday, the day of the week number for this date would be 6:

WEEKDAY(A2,2)

By subtracting 6 from the date in A2 and adding 1 to it, we get the week start date:

=A2-WEEKDAY(A2,2)+1

By adding 7, we get the week end date:

=A2-WEEKDAY(A2,2)+7

Still confused? Don’t worry! Check out my tutorial on How to Find Week Start Date and End Date in Google Sheets with Formula for a more detailed explanation.

We have completed the major part of writing our formula to summarize data by week start and end dates in Google Sheets.

QUERY to Summarize Data by Week Start and End Dates in Google Sheets

For now, let’s keep those helper columns. We will use the powerful QUERY function in Google Sheets to summarize our data by week start and end dates.

In cell F2, enter the following QUERY formula, which uses the data in the helper columns C and D:

FORMULA # 1: Finite Range and Helper Columns

=query( {C2:D20,B2:B20}, "Select Col1,Col2,sum(Col3) group by Col1,Col2 label sum(Col3)''" )

Formula Explanation

  • {C2:D20,B2:B20} – Query Data
    • This includes three columns: Week start dates, Week end dates, and the Amount column.
  • The QUERY function sums column 3 (Amount) by grouping columns 1 (Week starts) and 2 (Week Ends).

image # 3

Removing Helper Columns and Making the Range Infinite (Open)

I hope you have learned how to summarize data by week start and end dates in Google Sheets in a finite (closed) range.
Now, here are the steps to remove the helper columns and use the formula in an infinite (open) range.

  1. Remove the helper columns by combining the corresponding formulas in C2 and D2.

    • Cut the formula in D2 and modify the formula in C2 by appending the D2 formula to it: 
      =ArrayFormula( {(A2:A20-WEEKDAY(A2:A20,2)+1), (A2:A20-WEEKDAY(A2:A20,2)+7)} )

  2. Replace the range C2:D20 in the Query formula with the above combination formula.

FORMULA # 2: Finite Range but No Helper Columns

=ArrayFormula( query( { {(A2:A20-WEEKDAY(A2:A20,2)+1), (A2:A20-WEEKDAY(A2:A20,2)+7)}, B2:B20 }, "Select Col1,Col2,sum(Col3) group by Col1,Col2 label sum(Col3)''" ) )

Now you can feel free to remove or empty columns C and D (column D may already be blank as we have cut the formula).

The next step is to make the range infinite by changing A2:A20 and B2:B20 to A2:A and B2:B. Here are the required changes:

  • Replace (A2:A20-WEEKDAY(A2:A20,2)+1) with if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1).
  • Replace (A2:A20-WEEKDAY(A2:A20,2)+7) with if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7).
  • Modify B2:B20 to B2:B.

You’re almost ready! Since the formula is for an infinite range, include the WHERE clause "where Col1 is not null" before the GROUP BY clause in QUERY. So, the final formula is:

FORMULA # 3: Infinite Range Without Helper Columns

=ArrayFormula( query( { {if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1), if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7)}, B2:B }, "Select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label sum(Col3)''" ) )

You May Also Like: What is the Correct Clause Order in Google Sheets Query?

Here’s an additional tip for you:

The result you get from the above formula (as shown in image # 3) may have some differences compared to the result I showed at the beginning of this post (image # 1).

In image # 1, you can see week start and end dates in chronological order without missing any weeks. We achieve this by taking the min date and max date in A2:A and generating all the weeks during that period. As a result, there would be some weeks with zero amounts, which we call zero transaction weeks.

So, how do we achieve this?

Let’s start from the beginning. Copy the sample data from A2:B20 to a new sheet. In that sheet, we can write our new formula.

Step # 1

Insert the following formulas in cell C2:

=sequence( days( max(A2:A),min(A2:A) )+1, 1, min(A2:A) )

And in cell D2:

=ArrayFormula( C2:C- WEEKDAY(C2:C,2)+1 )

And in cell E2:

=ArrayFormula( C2:C- WEEKDAY(C2:C,2)+7 )

The C2 formula will expand the dates from the Min date in A2:A to the Max date in A2:A. The D2 and E2 formulas return the week start and end dates of the expanded dates.

image # 4

Step # 2

Replace C2:C in the above last two formulas with the corresponding formula in C2.

So the formula in D2 will be:

=ArrayFormula( sequence( days(max(A2:A),min(A2:A))+1,1,min(A2:A) )- WEEKDAY( sequence( days(max(A2:A),min(A2:A))+1,1,min(A2:A) ),2 )+1 )

And E2 will be:

=ArrayFormula( sequence( days(max(A2:A),min(A2:A))+1,1,min(A2:A) )- WEEKDAY( sequence( days(max(A2:A),min(A2:A))+1,1,min(A2:A) ),2 )+7 )

Now modify the formula in C2 as follows:

=ArrayFormula( sequence( days(max(A2:A),min(A2:A) )+1, 1, min(A2:A) )*0 )

This will return a column with 0 values. The purpose of all these steps will become clear as we move forward.

Step # 3

Let’s combine the above three columns using the following generic formula:

=ArrayFormula({D2:D,E2:E,C2:C})

This will return the values in the following format: Week start dates, Week end dates, and a column with 0s.

=ArrayFormula( {sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))- WEEKDAY( sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2 )+1, sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))- WEEKDAY( sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2 )+7, sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))*0} )

Final Step to Summarize Data by Week Start and End Dates in Google Sheets

Here, we can use the same Formula # 3 that we used to summarize data by week start and end dates (in chronological order but only with transaction weeks).

Simply add the above formula as additional rows with the Query ‘data’. Put a semicolon after B2:B and add the above formula.

=ArrayFormula( query( {{if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+1), if(datevalue(A2:A)>0,A2:A-WEEKDAY(A2:A,2)+7)}, B2:B; {sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))- WEEKDAY( sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2 )+1, sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))- WEEKDAY( sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A)),2 )+7, sequence(days(max(A2:A),min(A2:A))+1,1,min(A2:A))*0}}, "Select Col1,Col2,sum(Col3) where Col1 is not null group by Col1,Col2 label sum(Col3)''" ) )

Sample_Sheet_1920

That’s all! Now you’re ready to summarize data by week start and end dates in Google Sheets like a pro. Enjoy!

GiGiKing

Related posts