Windows 7 - IF and IF and IF

Asked By Ramona88 on 09-Aug-12 07:45 AM
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




--
Ramona88




Claus Busch replied to Ramona88 on 09-Aug-12 10:47 AM
Hi Ramona,

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


you do not 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
Stan Brown replied to Ramona88 on 10-Aug-12 02:31 AM
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...
Ramona88 replied to Stan Brown on 10-Aug-12 07:47 AM
@Claus Busch
I tried your formula but it does not give me the correct output. Thank
you anyway.

@Stan Brown
I tried this but do not 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




--
Ramona88
Claus Busch replied to Ramona88 on 10-Aug-12 11:15 AM
Hi Ramona,

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


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
Stan Brown replied to Ramona88 on 11-Aug-12 02:03 AM
What's with the @ signs?  Please quote correctly:
http://oakroadsystems.com/genl/unice.htm#quote


(And from your previous article, which you should have quoted but
did not):


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 is

=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...