0

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

Improve this question

1 Answer 1

Reset to default
1

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

Snapshot No Helper

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

Snapshot

Improve this answer
2
  • 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.
    – Tedinoz
    Apr 19, 2021 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.