Formula To Show The Most Common Or Frequent Day For An Entry

[aimheart]

Just new to this and have a query!

I have a list of people with corresponding dates and I want excel to take the DAY from the date and show me what day is most common day for each person. I.e if a person is against 14-Jul-2014; 14-Aug-2014 and 15-Sep-2014 the cell would show 14th as the most common date for that person. I have been looking at MODE; COUNTIF; SUMIF and DAY commands along with date formats/suffix but haven't cracked it as yet.

1534

[Excel Fox]

Use this as an array formula

=IFERROR("Day "&MODE(IF($B$2:$B$36=D2,DAY($A$2:$A$36),"")),"All are unique")

Sample attached

[aimheart]

Excellent this appears to be working for me! Just one question however, is there a way to remove "Day " and replace with the date suffix instead? E.g 01st; 04th; 11th; 13th instead of Day 1; Day 4; Day 11; Day 13th?

I did notice this from previously


Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
Here is an Excel function that can be used in a cell formula to apply the proper ordinal suffix to the number specified (in cell A1 for this example)...

MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

More than likely, you will use this function within an Excel formula by concatenating it after the number that is specified with in the function. For example, if cell B1 contains the number, then your formula could be something like this...

="Ordinal for B1: "&B1&MID("thstndrdth",MIN(9,2*RIGHT(B1)*(MOD(B 1-11,100)>2)+1),2)



- just not sure about incorperating it into the previous formula!

[Excel Fox]

Try this in E2 as array formula, and drag down

=IFERROR(MODE(IF($B$2:$B$36=D2,DAY($A$2:$A$36),""))&MID("thstndrdth",MIN(9,2*RIGHT(MODE(IF($B$2:$B$36=D2,DAY($A$2:$A$3 6),"")))*(MOD(MODE(IF($B$2:$B$36=D2,DAY($A$2:$A$36),""))-11,100)>2)+1),2),"All are unique")