How to add SIMPLISTIC Hrs./Day and Avg Hrs./Week columns in Google Sheets using this simple data?

sonar 0
Viewed 58

My sheet looks like this:

enter image description here

.csv code:

Date,Project name,Minutes spent,Time Start,Time End,Day
09/09/2022,Tast I,120,11:30 AM,1:30 PM,Friday
09/09/2022,Tast II,120,1:30 PM,3:30 PM,Friday
09/09/2022,Tast II,90,3:30 PM,5:00 PM,Friday
09/09/2022,Tast II,120,6:30 PM,8:30 PM,Friday
10/09/2022,Tast III,120,8:30 AM,10:30 AM,Saturday
10/09/2022,Tast III,120,10:30 AM,12:30 PM,Saturday
10/09/2022,Tast III,120,12:30 PM,2:30 PM,Saturday
10/09/2022,Tast III,150,2:30 PM,5:00 PM,Saturday
10/09/2022,Tast III,210,6:30 PM,10:00 PM,Saturday
11/09/2022,,,,,Sunday
12/09/2022,Tast IV,120,8:30 AM,10:30 AM,Monday
12/09/2022,Tast IV,90,10:30 AM,12:00 PM,Monday
12/09/2022,Tast V,120,12:00 PM,2:00 PM,Monday
12/09/2022,Tast V,180,2:00 PM,5:00 PM,Monday

I want to add these two more columns showing hrs/day and total hours/week (including Sundays, I sometimes have to work on those):

enter image description here

.csv code:

Date,Project name,Minutes spent,Time Start,Time End,Day,Hrs./Day,Total Hrs./ Week
09/09/2022,Tast I,120,11:30 AM,1:30 PM,Friday,7.5,19.5
09/09/2022,Tast II,120,1:30 PM,3:30 PM,Friday,7.5,19.5
09/09/2022,Tast II,90,3:30 PM,5:00 PM,Friday,7.5,19.5
09/09/2022,Tast II,120,6:30 PM,8:30 PM,Friday,7.5,19.5
10/09/2022,Tast III,120,8:30 AM,10:30 AM,Saturday,12,19.5
10/09/2022,Tast III,120,10:30 AM,12:30 PM,Saturday,12,19.5
10/09/2022,Tast III,120,12:30 PM,2:30 PM,Saturday,12,19.5
10/09/2022,Tast III,150,2:30 PM,5:00 PM,Saturday,12,19.5
10/09/2022,Tast III,210,6:30 PM,10:00 PM,Saturday,12,19.5
11/09/2022,,,,,Sunday,0,8.5
12/09/2022,Tast IV,120,8:30 AM,10:30 AM,Monday,8.5,8.5
12/09/2022,Tast IV,90,10:30 AM,12:00 PM,Monday,8.5,8.5
12/09/2022,Tast V,120,12:00 PM,2:00 PM,Monday,8.5,8.5
12/09/2022,Tast V,180,2:00 PM,5:00 PM,Monday,8.5,8.5

I'm new to this and looks like I can't post images yet. Please use the .csv file to help. Thanks.

Edit: Tast = Task.

google-sheets-formula google-sheets
1 Answers

For Hrs./Day column.

=BYROW(FILTER(A2:A,A2:A<>""),LAMBDA(x,SUM(FILTER(INDEX((E2:E-D2:D)*24),A2:A=x))))

For Total Hrs./ Week column.

=BYROW(FILTER(A2:A,A2:A<>""),LAMBDA(x,SUM(FILTER(INDEX((E2:E-D2:D)*24),INDEX(WEEKNUM(A2:A))=WEEKNUM(x)))))

enter image description here

Harun24hr0
Related