Generating worked hours by name and date in Google Sheets

Question

I'm working on making a tool which pulls hours worked by a specific person in a specific date range.

Right now, we have about 5 people who input their worked hours into separate sheets. Every sheet has a tab per month so for example:

Bob's sheet

- • January tab • February tab • March tab • etc.

Mary's sheet

- • January tab • February tab • March tab • etc.

I've tried to import hours for October which worked, but adding another month by combining the formula's breaks the entire formula when I'm not looking in all months of the formula.

I'm also facing a problem with making it possible to select a name and only sum those worked hours.

={FILTER(IMPORTRANGE("BOBS_URL";"September!A3:E500");IMPORTRANGE("BOBS_URL";"September!A3:A500")>A5;IMPORTRANGE("BOBS_URL";"September!A3:A500")<=B5)
;FILTER(IMPORTRANGE("BOBS_URL";"October!A3:E500");IMPORTRANGE("BOBS_URL";"October!A3:A500")>A5;IMPORTRANGE("BOBS_URL";"October!A3:A500")<=B5)}

I replaced the URL with "BOBS_URL". The code is also made up in dutch Excel, so the formatting is a bit different.

I expect to fill in a name at A1, a date "from" on A2 and a date "to" on B2. Then I want Google Sheets to import the requested data from another sheet which has a date, ordernumber, company name, assigment and hours in columns next to eachother.

player0 player0 · Accepted Answer · 2019-11-01T11:45:47

try:

={FILTER(IMPORTRANGE(A1; "September!A3:E500");
         IMPORTRANGE(A1; "September!A3:A500")>A2;
         IMPORTRANGE(A1; "September!A3:A500")<=B2)\
  FILTER(IMPORTRANGE(A1; "October!A3:E500");
         IMPORTRANGE(A1; "October!A3:A500")>A2;
         IMPORTRANGE(A1; "October!A3:A500")<=B2)}

where A1 contains (dropdown) BOBS_URL and A2/B2 are dates

but better would be to use QUERY:

=QUERY({
 IMPORTRANGE(A1; "September!A3:E500")\
 IMPORTRANGE(A1; "October!A3:E500")}; 
"where Col1 >  date '"&TEXT(A2; "yyyy-mm-dd")&"' 
   and Col1 <= date '"&TEXT(B2; "yyyy-mm-dd")&"'"; 0)

Generating worked hours by name and date in Google Sheets

1 Answers