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!

this formula:
=MAX(IF(($A$1:$A$5=E1)*($B$1:$B$5="A"),$C$1:$C$5," "))-MIN(IF(($A$1:$A
$5=E1)*($B$1:$B$5="A"),$C$1:$C$5+0,""))
(CTRL+SHIFT+ENTER)
shows 7 for Sally and 6 for Bob

did not get yr explanation re TODAY function


On 29 Gru, 18:16, 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!

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!

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

Hi,

" Presumably the max gap could also be between the start of the month
and the first occurrence"

In which case the current date is irrelevant

Cheers,
Shane Devenshire

"barry houdini" wrote in message
...
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

Hello Shane,

I'm not sure how you come to that conclusion from my comment.

If I'm reading it correctly the current date isn't irrelevant. I'm
assuming that Sean wants to get the maximum gap between instances of
"EventA" for Bob, given a start date of 1st of the month and end date
of "today". So the maximum gap could be between actual instances (as
with Sally) or could be between the last occurence and today (as with
Bob). I also assumed that the max gap could be between the 1st of the
month and the first occurence, if that was greater than any other gap,
but I've been wrong before........