Sort timesheet logged hours by week

Question

So I have a timesheet for a project I'm working on, and I'd like to see how many hours I've worked by week.

I found an answer from this post: Create Weekly/Monthly/Yearly line chart in Google Sheets to sum amounts within same week/month/year range which got me really far.

Monthly and Yearly queries are working great thanks to user player0. However, weekly sorting is borked for me. The code I'm using now comes from that post:

=ARRAY_CONSTRAIN(ARRAYFORMULA( 
IF(J2="Weekly", QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+WEEKNUM(A2:A)*0.083}, 
"select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
IF(J2="Monthly", QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
"select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
IF(J2="Yearly", QUERY({YEAR(A2:A), E2:E}, 
"select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)

Here's what the sheet looks like

I know very little about Google Sheets, so you'll have to keep it simple for me XD

Here ya go https://docs.google.com/spreadsheets/d/1BQ5OqqSXJ_0GMdoLuL62i8k4kFgeKYBrXAbXfaH1zOs/edit?usp=sharing Let me know if you'd like edit permissions — Jonah Grimm, Jun 22 '20 at 22:24

score 1 · Accepted Answer · answered Jun 22 '20 at 22:40

try:

=ARRAY_CONSTRAIN(ARRAYFORMULA( 
 IF(J2="Weekly",  QUERY(QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), E2:E, "w"&YEAR(A2:A)&TEXT(WEEKNUM(A2:A), "00")}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), "offset 1", 0), 
 IF(J2="Monthly", QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), E2:E, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(J2="Yearly",  QUERY({YEAR(A2:A), E2:E}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)

Sort timesheet logged hours by week

1 Answers1

Linked