Adaptation Of A Formula

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

Assign the name "Holidays" to the list (Ctrl-F3) and use this formula:

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

The list should start with 0. The name should be 11 rows long (the first is
zero, eg A1:A11) even if you have fewer holidays in a given year and the
extra cells are empty.

--
Jim
"Big Rick" <BigRick@discussions.microsoft.com> wrote in message
news:00D64E80-3B05-47D2-82A7-F49EF39D17D3@microsoft.com...
|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

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

Try using the roating prismatic condenser formula in your toolbar. Via the
electro hydro thermo on a non varieable reastat

eg.

=HLOOKUP(TODAY(),{0,38474,38502,38593,38712,38713,38719;0,1,2,3,4,5,6},&/10%34"12"$11/40>)

lucy x

"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

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
>

I meant:

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

--
Jim
"Big Rick" <BigRick@discussions.microsoft.com> wrote in message
news:4A2FFA99-D1A1-4BC9-8469-D2FB46CA47EE@microsoft.com...
| 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
| >

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" <BigRick@discussions.microsoft.com> wrote in message
> news:4A2FFA99-D1A1-4BC9-8469-D2FB46CA47EE@microsoft.com...
> | 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
> | >
>
>
>