How do i get google sheets add up the hours by automatically selecting the correct date range (Monday to Sunday)

Question

In Google Sheets, I'm trying to add up the hours (column F) for each week and automatically select the correct date range (Monday to Sunday). The start date may be in the middle of a week, and I want it to end the Sunday after the start date, then repeat the cycle until it's not populated. For example, Week1 is from Thursday to Sunday, and the following Monday to Sunday needs to Week2, etc.

If this is a duplicate I apologize; I couldn't find anything that answered it.

Example file

player0 player0 · Accepted Answer · 2020-02-03T16:43:44

try:

=ARRAYFORMULA(QUERY({WEEKNUM(A2:A22, 2), F2:F22}, 
 "select sum(Col2) 
  where Col2 is not null
  group by Col1 
  label sum(Col2)''"))

How do i get google sheets add up the hours by automatically selecting the correct date range (Monday to Sunday)

1 Answers