1

I'm working with a sheet that has 1,000' of dates and times in one cell where the formatting for each specific cell is Month/Day/Year - Hour:Minute AM or PM.

I'm looking to push out with a single line formula the most frequent days repeating and also the most frequent times that are repeating and capping them to 5 for each of the results.

I'm thinking possibly a Query or Array to setup but somewhat stuck so thought I'd reach out for some help..

UPDATE: I have all the data in a single column and need to identify the rounded time in 30 minute blocks for the data in the column. I pushed helper columns to see the data but the final single cell formula only outputs data after the first result. For some reason the first result is blank... Sheet Snapshot

Improve this question
1
  • The source data format is unclear, and it is also unclear whether you want days of week and times of day by frequency, or if you want streaks of a day or a time repeating in a row. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. Use the blank sheet maker to share safely.
    – doubleunary
    Commented Jun 29 at 4:30

1 Answer 1

Reset to default
1

Assuming that the data is one long text string in cell A1, you can convert it to a list of numeric date time values with this formula in cell A2:

=query( 
  arrayformula(transpose(value(regexreplace( 
    split( 
      regexreplace(regexreplace(
        A1, " - ", "-"), 
        " (A|P)|", "$1"), 
      " " 
    ), 
    "-", " " 
  )))), 
  "format Col1 'yyyy-MM-dd h:mm am/pm' ", 
  0 
)

Then, assuming that column A2:A contains valid numeric date time values, you can get a list of the five most frequently occurring days of week like this:

=query( 
  filter(text(A2:A, "DDDD"), isnumber(A2:A)), 
  "select Col1, count(Col1) 
   group by Col1 
   order by count(Col1) desc 
   limit 5", 
  0 
)

To get a similar list of the five most frequently occurring times of day by hour and minute, use text(A2:A, "hh:mm am/pm").

enter image description here

See query(), regexreplace(), split(), filter(), text() and isnumber().

There are two questions asked. To solve the "update", use this formula:

=arrayformula(let( 
  data, to_text(query( 
    hstack(
      text(Parabolic!B2:B, "dddd"), 
      text(mround(Parabolic!B2:B, "0:30"), "h:mm am/pm"), 
      Parabolic!B2:B 
    ), 
    "select Col1, Col2, count(Col3) 
     where Col1 is not null 
     and Col2 is not null 
     group by Col1, Col2 
     order by count(Col3) desc 
     limit 2 
     label count(Col3) '' 
     format count(Col3) '""(""0"")""' ", 
    0 
  )), 
  join(char(10), 
    byrow(data, lambda(row, join(" ", row))) 
  ) 
))

See cell J2 in the sample spreadsheet.

Improve this answer
4
  • Here's the data set that I have. 6/27/24 - 8:30 AM 6/27/24 - 4:30 PM 6/27/24 - 4:00 PM 6/27/24 - 3:45 PM 6/27/24 - 8:30 AM 6/27/24 - 8:06 AM 6/26/24 - 4:41 PM 6/26/24 - 8:00 AM 6/26/24 - 4:00 AM 6/26/24 - 8:00 AM 6/26/24 - 5:00 AM 6/26/24 - 4:25 AM 6/25/24 - 8:30 AM 6/25/24 - 6:00 AM 6/24/24 - 6:40 PM 6/24/24 - 4:34 AM 6/21/24 - 4:00 PM 6/21/24 - 9:00 AM There are an additional 100 rows after this. I am trying to find the 5 most frequent days based on 30 minute time blocks.
    – RTG
    Commented Jul 1 at 9:57
  • Those values are not in any standard format recognized by Google Sheets. Please edit the question and add any relevant details there. Use a table. Consider using the blank sheet maker.
    – doubleunary
    Commented Jul 1 at 11:37
  • You can use this link to the sheet.
    – RTG
    Commented Jul 2 at 5:40
  • Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead.
    – doubleunary
    Commented Jul 2 at 7:24

Your Answer

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

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