Whats the best way ?

[MAS]

I am happy to put in the work to get my head around visual basic 'Again' if
I have too but if I can do the following with formulas so much the better.

In brief I have the following table

Name Date Years Leave
John 01/08/01 2
Fred 22/05/89 14


So you see John started with the company on 01/08/01 and that means he has
completed 2 full years by the start of the financial year 01/04/04.

To know the value in I have to somehow get it to work through the following
information.

Less than 5 years = 24 days
5 years but less than 10 years = 26 days
10 years but less than 15 years = 28 days
15 years but less than 20 years = 29 days
20 years and over = 30 days

Clearly the value in the cell under leave for john would be 24 and under
Fred it would be 28.

Is this too hard for a formula, should I be looking to VB ?

any guidance would help.

M.

 

[Guest]

MAS

There's no really best way for almost anything in XL, and
there is probably a way better way than this one:

=IF(CEILING(D2+1,5)=5,24,IF(CEILING(D2+1,5)=10,26,IF
(CEILING(D2+1,5)=15,28,IF(CEILING(D2+1,5)=20,29,30))))

But it works...

Cheers
Juan

 

[Domenic]

Hi,

Using your table as an example, and assuming you have it on Sheet 1, try
the following:

1) Set-up a table on Sheet 2, something like this:

Years Days
0 24
5 26
10 28
15 29
20 30

2) Enter the following formula in D2 of your table on Sheet 1, and copy
down as far as you need to:

=VLOOKUP(C2,Sheet2!$A$2:$B$6,2)

Hope this helps!

 

[David McRitchie]

A little shorter than Juan's reply
D2: =CHOOSE(MIN(INT(C2/5)+1,5),24,26,28,29,30)

and if there is any question on how to calculate the years
see Chip Pearson's page
http://www.cpearson.com/excel/datedif.htm

 

[MAS]

Guy's..

What Can I say, they both answer my question and both work well.

It's an age since I did any real work in VBA and then it was in ACCESS so I
was not looking forward to spending weeks getting back into it. You have
saved me the trouble and given me the incentive to start looking at
understanding ALL the funtions in EXCEL, not just the few I use every day.

Cheers.

MAS

 

[Harlan Grove]

A little shorter than Juan's reply
D2: =CHOOSE(MIN(INT(C2/5)+1,5),24,26,28,29,30)

...

If the goal is minimal but adaptable,

=24+LOOKUP(C2,{0;5;10;15;20},{0,2,4,5,6})

 

[Dana DeLouis]

Just for fun. I think this can beat it by 7 characters, but it is
definitely not flexible. It assumes Integers in A1.

=MOD(7668316,34+INT(MIN(A1,20)/5))

Dana DeLouis

 

[Harlan Grove]

Just for fun. I think this can beat it by 7 characters, but it is
definitely not flexible. It assumes Integers in A1.

=MOD(7668316,34+INT(MIN(A1,20)/5))

....

You should have used MIN(INT(A1/5),4) to save one more character.

 

[Dana DeLouis]

Using multiple IF's is a good idea. This technique just tries to shorten
the length of your characters, that's all. I can never get it right, so
this was one suggestion from a custom Fourier program.

=27-SIGN(4.5-A1)-SIGN(9.5-A1)-SIGN(14.5-A1)/2-SIGN(19.5-A1)/2

=IF(CEILING(D2+1,5)=5,24,IF(CEILING(D2+1,5)=10,26,IF
(CEILING(D2+1,5)=15,28,IF(CEILING(D2+1,5)=20,29,30))))

Dana DeLouis

 

[Dana DeLouis]

Another option to multiple If's:

=24+2*(A1>=5)+2*(A1>=10)+(A1>=15)+(A1>=20)

Dana DeLouis

 

[Harlan Grove]

Another option to multiple If's:

=24+2*(A1>=5)+2*(A1>=10)+(A1>=15)+(A1>=20)

...

Or

=24+2*((A1>=5)+(A1>=10))+(A1>=15)+(A1>=20)

which doesn't save any keystrokes, but uses one fewer multiplication.