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

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

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 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 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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