Date dependant values.

B

Big Rick

Up to 10 times a year, I want to add a value of one to cell V4 starting with
zero.
I have come up with the following formula
=IF(TODAY()<38530,(0),IF(TODAY()<38540,(1),IF(TODAY()<38550,(2),IF(TODAY()<38560,(3),4)))) etc.
The problem is that I can only use the IF formula seven times.
I would very much appreciate it if you could write a new formula for me as I
am quite new to the whole thing and it took me weeks to work mine out!
(I have been advised to use the VLOOKUP formula but cannot get my head round
it.)
Thanking you in anticipation.
 
B

Big Rick

Hello Barb R.
In any one tax year (Apr to Mar.) there can be up to 10 Bank Holidays,
although this year, there are only 6. This is when I want to increment the
value of V4.
 
B

Barb R.

Another question. What does the value in V4 represent? The number of bank
holidays that have passed in the year?
 
N

N Harkawat

=HLOOKUP(TODAY(),{0,38530,38540,38550;0,1,2,3},2)
Keep extending the list in ascending order for all the dates
 
B

Big Rick

Hello Barb R. again. Nice to speak to you.
Yes. Apr 4 2005 was the first day of this tax year. V4 is the number of Bank
Holidays that have passed in a tax year.
 
B

Big Rick

My heartfelt thanks to N Harkawat. This works better than I could of possibly
imagined. Many thanks also to Barb R for all her time and effort.
 
Top