Google-sheets – Rank days of the month that occur most

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-datesgoogle-sheets-query

I would like to know which days of the month customers have historically paid on the most. I would like to create a report that has days 1,2,3,4…31 all with a count next to each day for how many times it occurs in our date column. I would also like to do the same with the days of the week if possible. Any help would be greatly appreciated!

Best Answer

=ARRAYFORMULA(QUERY(DAY(INDIRECT("A1:A"&COUNTA(A1:A))), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0))

=ARRAYFORMULA({ROW(A1:A31), IFERROR(VLOOKUP(ROW(A1:A31), 
 QUERY(DAY(INDIRECT("A1:A"&COUNTA(A1:A))), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0), 2, 0), 0)})

=ARRAYFORMULA({{"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}, 
 IFERROR(VLOOKUP({"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}, 
 QUERY(TEXT(INDIRECT("A1:A"&COUNTA(A1:A)), "ddd"), 
 "select Col1,count(Col1) 
  group by Col1 
  label count(Col1)''", 0), 2, 0), 0)})

spreadsheet demo

Related Solutions

Google-sheets – How to use DCOUNTA and get the month from a column instead of the entire date in the criteria

You need to use regexreplace first to remove the time part of the time stamp, and then the Text() function will properly transform the way you want.

=TEXT(REGEXREPLACE("date timestamp string","\d+:\d+:\d+",""),"MM")

Note: the timestamp part of the formula - may need to be altered depending on what exactly your string looks like, if you post an example of the string your starting with - I can modify the regex for you to fit that criteria

ALso if you share an example sheet - we can incorporate your DCOUNTA or a COUNTIF alternative

Google Sheets – How to Fix QUERY Error for BY Column

Short answer

According to the documentation, BY is a reserved word, to use it as an identifier, it should be back-quoted.

Explanation

I think that it's a reserved word because it's used by two clauses: