IF and IF and IF

Hi there,

I need a formula which should be quite straight forward (in my head) but if I do it the way I think it should work it comes back with an error. I 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 -> 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?

Many thanks,
Ramona

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Thu, 9 Aug 2012 11:45:33 +0000, Ramona88 wrote:
>
> 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?

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

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

@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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Fri, 10 Aug 2012 11:47:23 +0000, Ramona88 wrote:
>
> @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.



--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

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 not sure how to do that.

Please can you give it another try for me?

I really appreciate it!

Ramona

On Mon, 24 Sep 2012 16:37:58 +0000, Ramona88 wrote:
>
> 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 not
> sure how to do that.
>
> Please can you give it another try for me?

Since you didn't quote anything from what you were responding to, I
have no idea what you're talking about.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...