Many thanks. I should of worked that one out for myself.
I am forever in your debt.
--
Big Rick


"Jim Rech" wrote:

I meant:

=MATCH(TODAY(),Holidays)-1&" of "&COUNT(Holidays)-1

--
Jim
"Big Rick" wrote in message
...
| Many thanks to you both. I have used the first of Daves formula.
| To Jim. Yours didnt quite work out. I think it was counting the 0 as an
| actual holiday and was giving me 3 out of 7 instead of 3 out of 6.
| I truly appreciate your time and effort from you both.
|
| Dear Lucy.
| Am I supposed to laugh ?
| --
| Big Rick
|
|
| "Dave Peterson" wrote:
|
| 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
|