Hi Harlan,
Yes, there are standard dates in 'B', and as time goes on, the column will
fill up with sequential dates.
I'm off home now but I shall tackle this again tom.
--
Traa Dy Liooar

Jock


"Harlan Grove" wrote:

Jock wrote...
....
In column X, should each cell show a week number (1 down to 52(53)
or day number of the week (1 down to 365 or so in increments of 7)?
....
I get #N/A in every cell in Y
....
"Harlan Grove" wrote:
....
X1:
1

X2:
=X1+1

Week numbers from 1 to 53

Y1:
=MATCH(FDOY+7*(X1-1),Chancery!$B$8:$B$9997,0)

Note: I had defined the name FDOY to be the date of the first day of
the year. In Y1, X1 = 0, so the 1st arg to MATCH evaluates to the
first day of the year.

Y2:
=IF(X2-X$1<365+(DAY(X$1)<DAY(X$1)+365),MATCH(FDOY+7*(X2-1),
Chancery!$B$8:$B$9997,0),ROWS(Chancery!$B$8:$B$9 997)+1)

In Y2, X2 = 2, so the 1st arg to MATCH is 7 days after the 1st day of
the year, so the 1st day of the 2nd week of the year. And so on in col
Y through either Y53 or Y54. If the year in question is one of the
peculiar ones with 53 weeks, Y53 would evaluate to the 1st day of the
53rd week and Y54 to one more than the number of rows in Chancery!
B8:B9997, which I've been assuming is a range of dates. If the year in
question has 52 weeks, then both Y53 and Y54 should evaluate to one
more than the number of rows in Chancery!B8:B9997.

So, does Chancery!B8:B9997 contain standard date values?