0

This results in a nice table of sorted weekday, number of those weekdays and total from values in column A like 01/01/21 and column D like 123

=QUERY(Query(A2:D, "select (A), sum(D) 
                    where A is not null and dayOfWeek(A)<>1
                    group by (A) 
                    order by  dayOfWeek(A) "),
              "select dayofWeek(Col1), Count(Col1), Sum(Col2)
               group by dayofWeek(Col1) 
               label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', count(Col1) '#' 
               format dayOfWeek(Col1) 'dddd', sum(Col2) '###0.' ")

I would like to have another small table from the same values in column A and D but format it like 2021 01 Jan (i.e. "yyyy mm mmm")

The output should be just one cell by month, for example 2021 01 Jan would be one cell as summary of all the multiple January dates in column A in format 01/01/21 .. 01/30/21 etc..

When I apply that to the format function it results in 1900 01 Jan

=QUERY(Query(A2:D, "select (A), sum(D) 
                    where A is not null and dayOfWeek(A)<>1
                    group by (A) 
                    order by  dayOfWeek(A) "),
              "select dayofWeek(Col1), Count(Col1), Sum(Col2)
               group by dayofWeek(Col1) 
               label dayOfWeek(Col1) 'Weekday', sum(Col2) 'Total', count(Col1) '#' 
               format dayOfWeek(Col1) 'yyyy mm mmm', sum(Col2) '###0.' ")

https://docs.google.com/spreadsheets/d/18uNWEAqepsjNVuTxggBOp1gOlf_GqYvuFYh-1F5r774/edit?usp=sharing

Your Answer

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

Browse other questions tagged or ask your own question.