On Dec 29, 6:53*pm, Shane Devenshire
wrote:
Hi,

Your title states finding the MAX difference, but your example finds the MIN
difference. *Here is a formula for the MAX difference:

=MAX(($A$2:$A$6="Bob")*($D$1-$C$2:$C$6))

This is and array formula so press Shift+Ctrl+Enter to enter it.
D1 contains the date 1/15/2009. *

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire



"Sean Timmons" wrote:
So, hoping I can explain this tersely.

Let's say today is the 15th of January.

I have records such as the below.

Col A * * * * * * * * Col B * * Col C
Rep Names * * * *Event * * Date of Event
Bob * * * * * * * * *EventA * * 1/1/09
Sally * * * * * * * * EventA * * 1/5/09
Bob * * * * * * * * *EventB * * *1/6/09
Bob * * * * * * * * *EventA * * *1/7/09
Sally * * * * * * * *EventA * * *1/12/09

I have a separate table with each rep name in column A

The report will be only for the month of January.

In column B, I want to know, for each rep, the maximum number of days
between EventA.

So, for Bob, it would be 8. This because he had an EventA on 1/7, and today
is 1/15. So, I know we need to use the DAY() function within to determine
current day of month.

For Sally, the number would be 7 since eventA was 5th and 12th.

I know the formula would have to basically figure out that, 15("today"'s day
of month)-7 = 8, 7-1 = 6 kind of thing. Seems like an array formula..

Hoping this wasn't overly complex to read... Let me know if any questions,
and thank you in advance for any attempts!- Hide quoted text -

- Show quoted text -

Presumably the max gap could also be between the start of the month
and the first occurence.

Try this formula

=MAX(FREQUENCY(ROW(INDIRECT("1:"&DAY(MIN(D1,DATE(Y EAR(C2),MONTH
(C2)+1,0)))+1))-1,IF(A2:A6="Bob",IF(B2:B6="EventA",DAY(C2:C6)))))

Where D1 is "today's" date.

This is an "array formula" that needs to be confirmed with CTRL+SHIFT
+ENTER

I assume that today must be greater than the latest date shown. The
formula also works OK if "today's" date is later than the end of the
month in question. Replace "Bob" and "EventA" with required criteria