# Count of weekdays and the time duration per day

I have a column that has 34 records of Week Day, Month/Day, and Times. I am looking for two formulas that I can use in a table that will give me the count of weekdays and the time duration per day. Eventually, I would like to just copy and past new dates into column A and have the table automatically calculate. Here is my google sheet example. Is there a way to do this without creating helper columns? If not, no big deal. Anything to help automate the process will be helpful.

https://docs.google.com/spreadsheets/d/1C6N94QJyEgm-2yg2SEDOweIU2fk2h2DLydKb-nH-ObE/edit?usp=sharing

## 1 Answer

There are likely to be several ways to answer your question. Please consider this as one approach.

You asked "Is there a way to do this without creating helper columns?"... Yes, though this provides an example of "no helper" and "helper".

NO HELPER

Count of Days

• Insert this formula in Cell D3:
• `=iferror(query(\$A\$2:\$A\$35,"select count(A) where dayOfWeek(A) contains '"& IFERROR(vlookup(C3,{{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},{1;2;3;4;5;6;7}},2,0)) &"' label count(A) ''"),0)`
• manually copy down to Cell D9

Time duration per day:

• Insert this formula in Cell E3:
• `=iferror(query(\$A\$2:\$A\$35,"select max(A) where dayOfWeek(A) contains '"& IFERROR(vlookup(C3,{{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},{1;2;3;4;5;6;7}},2,0)) &"' label max(A) ''")-query(\$A\$2:\$A\$35,"select min(A) where dayOfWeek(A) contains '"&IFERROR(vlookup(C3,{{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},{1;2;3;4;5;6;7}},2,0))&"' label min(A) ''"),0)`
• format as `Duration`
• manually copy down to Cell E9

Totals:

• Cells D10 and E10 are simple `SUM` formula
• format Cell E10 as `Duration`

HELPER COLUMN

• Insert a new column to the left of Column D (Count of Days)
• Header = `Day of week`
• Values: Sunday = `1`, Monday = `2` and so on.
• This column can be hidden

Day of the week:

• Insert this formula in Cell E3:
• `=iferror(query(\$A\$2:\$A\$35,"select count(A) where dayOfWeek(A)contains '"& D3 &"' label count(A) ''"),0)`
• manually copy down to Cell E9

Time duration per day:

• Insert this formula in Cell F3:
• `=iferror(query(\$A\$2:\$A\$35,"select max(A) where dayOfWeek(A) contains '"&D3&"' label max(A) ''")-query(\$A\$2:\$A\$35,"select min(A) where dayOfWeek(A) contains '"&D3&"' label min(A) ''"),0)`
• format as `Duration`
• manually copy down to Cell F9

Totals:

• Cells E10 and F10 are simple `SUM` formula
• format Cell E10 as `Duration`

• The question is "Is there a way to do this without creating helper columns?" but this answer use helper cells. Does it means that it's not possible? :) Commented Apr 18, 2021 at 17:21
• @Rubén "Does it means that it's not possible?" No - you shamed me into revisiting and revising the answer ;). But I'm willing to bet that there's a much more elegant solution than my revision. Commented Apr 19, 2021 at 10:13