# 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.

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? :)
– Rubén
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. Apr 19, 2021 at 10:13