# Thread: Interval between repeated events?

1. ## Interval between repeated events?

OK: column A contains a growing list of dates, at weekly intervals, about 5 instances of the same date every week.
Column B is a growing list of numbers, many of which repeat from time to time.
(To be specific, they're church hymn numbers used each week.)
In column C I want a formula returning a message like, "Last used x, y and z weeks ago" where the corresponding number in column B has indeed occurred previously, else "Not used before".
I can't figure out how to do this.
(The crude way is to filter on the number and read out the dates, but I'm after something smarter, if possible.)

2. John,

First you should change your userid to NOT include your email as you're just asking to get spammed!

Why not just use a Pivot Table which will give you a quick visual indication of the usage of hyms.

JohnPIvot.JPG

Sample File: JohnPivot.xlsx

HTH

3. John,

You can use a User Defined Function (UDF) to perform the calculations. In my example, in cell D1, ether the formula:

=Hymns(\$B\$1:\$B\$17) where \$B\$1:\$B\$17 is the range of hymns then copy down.

HTH,
Maud

DateDiff2.png

In a standard module, enter the code:
Code:
```Public Function Hymns(rng As Range) As String
Application.Volatile
'-------------------------------------
'DECLARE AND SET VARIABLES
Dim cell_1 As Range, cell As Range, str As String, Row As Integer
Row = Application.Caller.Row
str = "Last used: "
'-------------------------------------
'FIND NUMBER OF WEEKS AGO
For Each cell In rng
If cell = Cells(Row, 2) Then
str = str & DateDiff("ww", cell.Offset(0, -1), Date) & ", "
End If
Next cell
'-------------------------------------
'REMOVE TRAILING COMMA A RETURN WEEKS
str = Left(str, Len(str) - 2)
Hymns = str & " week(s) ago."
End Function```

4. Thank you both VERY much!

(RetiredGeek - point taken about email addy - but not intuitive to me how to change it?)

JRR

5. Hi JRR

..what you do is browse to this site, and Register again.
For your User Name, enter something like JRR, or Thorns, or anything you like.
Then fill in the rest of the registration details.

zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•