Printable View
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.
Attachment 1534
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
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
- just not sure about incorperating it into the previous formula!Quote:
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)
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$3 6=D2,DAY($A$2:$A$36),"")))*(MOD(MODE(IF($B$2:$B$36 =D2,DAY($A$2:$A$36),""))-11,100)>2)+1),2),"All are unique")