Help with formula

[Guest]

My problem is with the first year only! Can't figure out how to write this
formula with certain criteria within the first year. Which is: If B16(hire
date)< 90 days then "0" on all curcumstances, AFTER 90 days then If B16(hire
date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept
30th then "2". My problem lies when if you have 90 days completed when the
next year rolls around you have "3". Then it's "3" every year untll your 5th
year, then it's "4", every year after that. Example:
Here's what I have now which works fine untill Jan. 1st which should change
to "3" instead it stays at "2" untill a whole year is completed.
=IF(H4<2.4934,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(AND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)>6),2,3),4))
Thanks in Advance!

 

[Sandy Mann]

Ritchard,

date) is between Jan. 1st - June 30th then "3", If between July 1st - Sept
30th then "2".

What happens if the date is after 1st September? I assume you meant 1st
July - 31 December

30th then "2". My problem lies when if you have 90 days completed when
the
next year rolls around you have "3". Then it's "3" every year untll your
5th
year, then it's "4"

I assume that you want a date after 1st July to remain 2 until it is after 5
years if so then try:

=IF(H4<2.4934,0,IF(TODAY()-B16<=90,0,IF(DATEDIF(B16,TODAY(),"Y")<5,IF(MONTH(B16)>6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

I need it to change to "3" when the year changes to the next year if you have
90+ days the following year and stay at "3" untill the 6th year then change
to "4". Otherwise it's 0 or 2: Example: If start date is July 31, 2006 then
that person will have 2 days to use by Dec.31,2006 , Once Jan1, 2007 rolls
around it changes to "3" even though a whole year has not been completed.

 

[Sandy Mann]

OK try:

=4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)>6))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

Forgot the first two conditions make that:

=(4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)>6)))*(AND(H4>2.4934,TODAY()-B16>90))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Yes that got it! You are amazing!!! I've worked for months on this and no one
at work even came close. Thanks again