Microsoft Office 2007 professional (aca...

=IF(OR(B6="FT",B6="PT",IF(AND(D6=1),$Z$14,IF(AND(D6>=1,D6<=1.5)$Z$15,))) I am trying to determine the formula if emplyees FT who have worked for more than 1 year Get 5 Days for family leave and employees who are FT for less than 1 year get 3 days for family leave and PT employees who have worked for more than 1.5 years get 3 days -everyone else gets 0

StatusDate Hired Years Employed Vacation Leave Used Vacation Leave Remaining Vacation Leave Family Leave

FT 8/23/2007 2.9 10 5 5

FT 5/7/2010 0.2 0 0 0

FT 10/6/2008 1.7 5 4 1

FT 5/15/2008 2.1 10 3 7

PT 3/19/2009 1.3 FALSE 0 0I have set up

5 in Z1 and 3 in Z2 and 0 in Z3

I'd like to start with suggestions for using this forum: 1 - Don't post duplicate posts, they clog up the archives and make for messy threads. (I have deleted your other post)

2 - Please use a subject line that is relevant to the contents of your post. If everybody that had an Excel 2007 formula question used a subject line like Excel 2007 Formula, we wouldn't be able to tell one thread from another. (I edited your subject line)

3 - Try to use the pre tags when you post sample data. It helps line up the columns and makes the post easier to understand. e.g.

FT 8/23/2007 2.9 10 5 5 FT 5/7/2010 0.2 0 0 0 FT 10/6/2008 1.7 5 4 1 FT 5/15/2008 2.1 10 3 7 PT 3/19/2009 1.3 FALSE 0 0Thanks!

DerbyDad03

Office Forum Moderator

I came up with 2 different ways to solve this one... =IF(A6="FT",IF(C6>1,$Z$1,$Z$2),IF(A6="PT",

IF(C6>1.5,$Z$2,$Z$3),$Z$3))or

=IF(AND(A6="FT",C6>1),$Z$1,

IF(OR(AND(A6="FT",C6<1),

AND(A6="PT",C6>1.5)),$Z$2,$Z$3))I offer both of these solutions because I think you should know about the Formula Auditing tool in Excel. My guess is that you aren't aware of it.

Select a cell with a formula in it and click on Tools...Formula Auditing...Evaluate Formula.

Using this feature, you can click the Evaluate button and single step through the formula as it evaluates each part. It's a lot better than just entering stuff in a cell and not having any idea why you are not getting the results you expect.

Try it on both of the formula I offered. Try to understand why the various parts of the formula evaluate to "TRUE" and "FALSE".

I also think you need to review the Help files for IF, AND and OR. The structure you are using is improper in many cases and that is the main reason you are getting the wrong results and errors.

For example, you used AND(D6=1) in your formula.

AND is supposed to be used to determine if 2 or more conditions are TRUE. AND(D6=1) only checks one condition.

Ask Your Question

Weekly Poll