How do you find the most common day in a year in Google Sheets?

user_11243944 0
Viewed 30

I'm hard at work with a Google Sheets spreadsheet of a huge catalog of wiki edits I made over the years, and I'm having difficulties trying to find the most common day within a year. I've been able to check the most common day by checking with wikis and account, but I had no luck with going by year.

Here is the formula I developed to track most commonly edited day for a particular wiki:

=IFERROR(INDEX('In one timezone (UTC)'!E:E,MODE(IF('In one timezone (UTC)'!A:A="Nookipedia", MATCH('In one timezone (UTC)'!E:E,'In one timezone (UTC)'!E:E,0)))),"None")

And here was my attempt at trying to find the most commonly edited day of a particular year:

=IFERROR(INDEX('In one timezone (UTC)'!E:E,MODE(IF(YEAR('In one timezone (UTC)'!E:E)="2012", MATCH('In one timezone (UTC)'!E:E,'In one timezone (UTC)'!E:E,0)))),"None")

formula google-sheets datetime
1 Answers

try:

=ARRAYFORMULA(ARRAY_CONSTRAIN(SORTN(QUERY({FILTER({B6:B, YEAR(B6:B)}, ISDATE_STRICT(B6:B))}, 
 "select Col1,Col2,count(Col1) 
  group by Col1,Col2,year(Col1) 
  order by Col2,count(Col1) 
  desc label count(Col1)''"), 9^9, 2, 2, 1), 9^9, 1))

enter image description here

user_56326290
Related