Deriving MODE from time

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
83
Hello -

I am trying to derive the MODE of a sequence of times. I have the following format to convert:

12:00:00 EDT

I imagine it does need to be converted Into text or number format so as to derive the MODE of this sequence. I have tried several approaches to no avail.

I also wish to derive the MODE of the day of the week, as well. I have converted the actual date into day of week with a custom format of, "ddd," and would like to derive the MODE of this (separate) sequence too.

Is there anyone with any ideas on how to proceed on either of these please?

Thanks much to you all —
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
Re: Deriving MODE from time help, please

@aiki100, it's hard to know what to suggest without seeing real data in context with sample desired result. But let's say your data (for either situation) was in A1:A20. You could try the following array formula (confirmed with Ctrl+Shift+Enter):

Code:
=INDEX(A1:A20,MODE(IF((A1:A20<>"")*ISNA(MATCH("",A1:A20,0)),MATCH(A1:A20,A1:A20,0))))
Adjust the five instances of the range in the formula to match your own data range.

Again, this only a guess based on what information was provided in your post. If it doesn't work, do try posting actual sample ranges with desired sample output.
 

Forum statistics

Threads
1,081,418
Messages
5,358,567
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top