IF and IF and IF

[Ramona88]

Hi there,

I need a formula which should be quite straight forward (in my head) bu
if I do it the way I think it should work it comes back with an error.
hope you can help me

I have the following fields:
Name
Start Date
Eligible (this should be a formula)
Today's Date

Criteria:
Employees that have been with the company for less than 1 year -> retur
0
Employees that have been with the company between 1 and 2 years -
return 3
Employees that have been with the company for 2+ years -> return 5

Is that do-able?

Many thanks,
Ramon

 

[Claus Busch]

Hi Ramona,

Am Thu, 9 Aug 2012 11:45:33 +0000 schrieb Ramona88:

I have the following fields:
Name
Start Date
Eligible (this should be a formula)
Today's Date

you don't need a column for today's date.
If Name is in A and Start Date in B, then try:
=VLOOKUP(DATEDIF(B2,TODAY(),"y"),{0,0;1,3;2,5},2,1)


Regards
Claus Busch

 

[Stan Brown]

Criteria:
Employees that have been with the company for less than 1 year -> return
0
Employees that have been with the company between 1 and 2 years ->
return 3
Employees that have been with the company for 2+ years -> return 5

Is that do-able?

Of course it is. What formula did you try?

 

[Ramona88]

@Claus Busch
I tried your formula but it doesn't give me the correct output. Than
you anyway.

@Stan Brown
I tried this but don't really know how to connect those two IFs so i
obviously gives me an error:

=IF(D4<=$F$1-365,2.5,0)AND(IF(D4>$F$1-730,5,2.5))

D4 -> Start Date
F1 -> Today's Dat

 

[Claus Busch]

Hi Ramona,

Am Fri, 10 Aug 2012 11:47:23 +0000 schrieb Ramona88:

@Claus Busch
I tried your formula but it doesn't give me the correct output. Thank
you anyway.

have another try:
=VLOOKUP(DATEDIF(B2,TODAY(),"y"),{0.0,1.3,2.5},2,1)


Regards
Claus Busch

 

[Stan Brown]

@Claus Busch
I tried your formula but it doesn't give me the correct output. Thank
you anyway.

What's with the @ signs? Please quote correctly:
http://oakroadsystems.com/genl/unice.htm#quote

@Stan Brown
I tried this but don't really know how to connect those two IFs so it
obviously gives me an error:

=IF(D4<=$F$1-365,2.5,0)AND(IF(D4>$F$1-730,5,2.5))

D4 -> Start Date
F1 -> Today's Date

(And from your previous article, which you should have quoted but
didn't):

Employees that have been with the company for less than 1 year ->
return 0
Employees that have been with the company between 1 and 2 years ->
return 3
Employees that have been with the company for 2+ years -> return 5

Think about what IF does: if a condition is true, return A; otherwise
return B. If you want to test multiple conditions, you have in
effect an if-then-else-if situation.

=IF(A1=1, "one", IF(A1=2, "two", IF(A1=3, "three", "none of the
above) ) )

See the pattern?

In your case it's

=IF(F1-D4<365, 1, IF(F1-D4<2*365, 3, 5) )

We're both ignoring leap years, by the way.

 

[Ramona88]

Hi Stan,

Sorry about the wrong quoting.

I tried your formula but it still doesn't work. It always gives back "3
for everything under 2 years.
Instead of the "<2*365" we need to say ">=365 and <2*365" but again no
sure how to do that.

Please can you give it another try for me?

I really appreciate it!

Ramon