Help with formula
 

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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!

Help with formula
 

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(B 16)6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Richard" wrote in message
...
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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!

Help with formula
 

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" wrote:

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(B 16)6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Richard" wrote in message
...
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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!

Help with formula
 

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


with @tiscali.co.uk


"Richard" wrote in message
...
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" wrote:

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(B 16)6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Richard" wrote in message
...
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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!

Help with formula
 

Forgot the first two conditions make that:

=(4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)))*(AND( H42.4934,TODAY()-B1690))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Richard" wrote in message
...
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" wrote:

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(B 16)6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Richard" wrote in message
...
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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!

Help with formula
 

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

"Sandy Mann" wrote:

Forgot the first two conditions make that:

=(4-(YEAR(TODAY())-YEAR(B16)<6)-(AND(YEAR(TODAY())=YEAR(B16),MONTH(B16)6)))*(AND( H42.4934,TODAY()-B1690))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


with @tiscali.co.uk


"Richard" wrote in message
...
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" wrote:

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(B 16)6,2,3),4)))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Richard" wrote in message
...
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(A ND(DATEDIF(B16,TODAY(),"Y")<1,MONTH(B16)6),2,3),4 ))
Thanks in Advance!