Find if date is within 5 months of previous 1

[DrSmyth]

Hi, I've been racking my brains trying to think of a way of doing this without using excel and some VBA, but not had any joy yet...

My data consists of a name, a date and an amount. The amounts have a one to one relationship with the dates so we can probably ignore them. Each name can have several dates. I need to flag each date that is within 5 months of the previous date for a name.

Heres an example of what I have:
Name Date
A 01/02/06
A 06/03/06
B 04/06/06
C 01/02/06
C 06/08/06
d 01/02/06
d 02/06/06
d 02/07/06

And here's what i'd like to see:
Name Date Flag
A 01/02/06
A 06/03/06 Y
B 04/06/06
C 01/02/06
C 06/08/06
d 01/02/06
d 02/06/06 Y
d 02/07/06 Y

As you can see for each name I need to find the number of months between each date and the one previous to it (if there is more than one date) and if this is 5 or under then i need to flag it with a Y.

Can anybody help...

P.S. Sorry if this isn't very clear, I'll be happy to add more detail if required..

 

[xlbo]

'lo Doc - can you not just use the MonthsBetween() & Previous() functions ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DrSmyth]

Previous() function, not seen that before, I'll take a look and get back to you...

 

[xlbo]

it's real good for ordered data sets:

=If(Previous(<Variable Name>)=<Variable Name>) then "Same Item" Else "New Item"

So you can use Previous() to check that the name is the same and then use it again to get the prior date to check against....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DrSmyth]

Cheers Geoff... Sounds like exactly what i need....

 

[DrSmyth]

Works like a dream... Have a star...

 

[xlbo]

no worries Doc - glad you got it sorted

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question