Help with a Nasty IF Statement

[Abarxax]

Hi Guys,



I was wondering if someone could give me a hand figuring out the logic for this formula.

Basically I need to find when an employees promotion date is I have their start date as well as the number of days worked.

Now I managed to figure out if I did Start Date + 730 - Today() which gave me the number of days til next promotion (the 730 is 2 years the first increment on the promotion scale) then I did the Start Date + Number of days til promotion (calculated from the above formula) to get my promotion date which works but however I need it to check that if they have worked more than 2 years check the next increment on the promotion scale.

If anyone has any thoughts I would be most appreciative it is driving me wild :P.

[Pecoflyer]

Hi and welcome

could you please be a little more specific and post a sample sheet showing BEFORE and AFTER ?

Be also aware that adding 730 days does not account for leap years. Is that important?

[BobBridges]

How many promotions are there, Abarxax? I'm thinking maybe a lookup would be more flexible. See the attached example.

The thing is, you have to embed a number of IFs for each promotion level. A lookup is easier to maintain and to follow. But if you still want to learn how to embed IF statements one inside the other, feel free to insist .

[Abarxax]

Thanks for your replies I have upload a copy of the spreadsheet.

[Pecoflyer]

Could you please
1 Get rid of the merged cells ( nothing but trouble, why MS implemented them is a mystery)
2 Add the BEFORE and AFTER you need in a visible way?

Thx

[Abarxax]

I have gotten rid of the the merged cells but I'm not entirely sure what you mean by a visual before and after?

[Pecoflyer]

Is it the next promotion date you are trying to find?

[Pecoflyer]

Does the attached ( see colored zones on the Employees tab ) go in the right direction?

[Abarxax]

Thanks for your reply it the highlighted section is the current pay grade they are on that seems to have fixed the issue but would you please explain the function for me so I can follow what you did sorry to be a pain I wasn't expecting such comprehensive help. it is greatly appreciated.

[Pecoflyer]

The LOOKUP¨function looks for the value TODAY()-M2 ( length of service) in the first range ( callled Lookup_vector).
If not found it looks for the first number larger than said value, and returns the value with the same position in the second range( called result_vector).
For this to work values in the lookup_vector must be placed in ascending order.

This link will also help.

An INDEX/MATCH combination might also work

[Abarxax]

Excellent Thanks for the help it is greatly appreciated.