One more if you only put one year's worth of data in sheet2.

=COUNTIF(Sheet2!A1:A11,"<="&TODAY()) &" of " & COUNT(sheet2!A1:A11)

If you kept adding dates to that list (keeping the old for historical reasons
and adding future for ease of updates????), you could use something like:

=SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())),
--(sheet2!A1:A1000<=today()))
&" of "&SUMPRODUCT(--(YEAR(sheet2!A1:A1000)=YEAR(today())))

(all one cell)


Big Rick wrote:

I have the following formula in a cell.
This gives me the number of how many Bank Holidays have passed in this tax
year.
=HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713, 38719;0,1,2,3,4,5,6},2)
(courtesy of N. Harkawat)

In any one tax year there can be up to 10 Bank Holidays. (This year there
are only 6) I would like the cell to read "1 of 6", "2 of 6" etc, but in an
automated way.

On a separate sheet, I could put in A1 to J1 for instance, the dates of the
Bank Holidays in the current tax year. In cells B1 to J1 I could have the
numbers 1 to 10.
(Or any other way or advice greatly appreciated)

I simply want to enter the dates of the Bank Holidays in the A row and have
the main cell (Holidays!$P$6) read "1 of 6" this year or "1 of 8" next year.
Hope you can understand this logic.

Thanking you in anticipation.
--
Big Rick

--

Dave Peterson