IF function based on True result with large formula.
 

I have a massive formula that I use to figure up vacation accumulation for
certain employees. Now I have to run the same formula EXCEPT it only needs
to be ran if the employee has worked 140 hrs. How can I incorporate that
into my formula??? Below is my formula-

IF A2 is =140 then

formula:
-------------------------------------------------------------------------------
=IF(AND(DAY(TODAY())=DAY(A3),YEAR(TODAY())=YEAR(A 3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(T ODAY())=YEAR(A3)),(MONTH(TODAY())-(MONTH(A3)+1))*3.34,IF(AND(YEAR(TODAY()YEAR(A3)), (YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())=DAY(A3)),(((MONTH(TODAY ()))*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),IF(AND(YEAR(TODAY()YEAR(A3)),( YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())<DAY(A3)),(((MONTH(TODAY( ))-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A"))))

IF function based on True result with large formula.
 

the formula is too long. try to put it into a UDF , and teh a simple IF
would work.

what is the intent on the formula - looks like a date calculation - and many
people here have a lot of experience that mightoffer a better solution.

"naiveprogrammer" wrote:

I have a massive formula that I use to figure up vacation accumulation for
certain employees. Now I have to run the same formula EXCEPT it only needs
to be ran if the employee has worked 140 hrs. How can I incorporate that
into my formula??? Below is my formula-

IF A2 is =140 then

formula:
--------------------------------------------------------------------------------
=IF(AND(DAY(TODAY())=DAY(A3),YEAR(TODAY())=YEAR(A 3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(T ODAY())=YEAR(A3)),(MONTH(TODAY())-(MONTH(A3)+1))*3.34,IF(AND(YEAR(TODAY()YEAR(A3)), (YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())=DAY(A3)),(((MONTH(TODAY ()))*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),IF(AND(YEAR(TODAY()YEAR(A3)),( YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())<DAY(A3)),(((MONTH(TODAY( ))-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A"))))

IF function based on True result with large formula.
 

The intent of the formula is to track the accrued vacation time for each
employee per their hire date. *During 1st year vacation employee will
accumulate at 3.34 a day for every month of service. (ie hire date = March 3,
2005- start accumulating April 3, 2005)*

Also I'm not familiar with UDF, what is this?

Thanks for replying!!!


"Patrick Molloy" wrote:

the formula is too long. try to put it into a UDF , and teh a simple IF
would work.

what is the intent on the formula - looks like a date calculation - and many
people here have a lot of experience that mightoffer a better solution.

"naiveprogrammer" wrote:

I have a massive formula that I use to figure up vacation accumulation for
certain employees. Now I have to run the same formula EXCEPT it only needs
to be ran if the employee has worked 140 hrs. How can I incorporate that
into my formula??? Below is my formula-

IF A2 is =140 then

formula:
--------------------------------------------------------------------------------
=IF(AND(DAY(TODAY())=DAY(A3),YEAR(TODAY())=YEAR(A 3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(T ODAY())=YEAR(A3)),(MONTH(TODAY())-(MONTH(A3)+1))*3.34,IF(AND(YEAR(TODAY()YEAR(A3)), (YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())=DAY(A3)),(((MONTH(TODAY ()))*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),IF(AND(YEAR(TODAY()YEAR(A3)),( YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())<DAY(A3)),(((MONTH(TODAY( ))-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A"))))

IF function based on True result with large formula.
 

UDF is User Defined Function
It will enable you to break your formula down so much more easily

"naiveprogrammer" wrote in
message ...
The intent of the formula is to track the accrued vacation time for each
employee per their hire date. *During 1st year vacation employee will
accumulate at 3.34 a day for every month of service. (ie hire date = March
3,
2005- start accumulating April 3, 2005)*

Also I'm not familiar with UDF, what is this?

Thanks for replying!!!


"Patrick Molloy" wrote:

the formula is too long. try to put it into a UDF , and teh a simple IF
would work.

what is the intent on the formula - looks like a date calculation - and
many
people here have a lot of experience that mightoffer a better solution.

"naiveprogrammer" wrote:

I have a massive formula that I use to figure up vacation accumulation
for
certain employees. Now I have to run the same formula EXCEPT it only
needs
to be ran if the employee has worked 140 hrs. How can I incorporate
that
into my formula??? Below is my formula-

IF A2 is =140 then

formula:
--------------------------------------------------------------------------------
=IF(AND(DAY(TODAY())=DAY(A3),YEAR(TODAY())=YEAR(A 3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(T ODAY())=YEAR(A3)),(MONTH(TODAY())-(MONTH(A3)+1))*3.34,IF(AND(YEAR(TODAY()YEAR(A3)), (YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())=DAY(A3)),(((MONTH(TODAY ()))*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),IF(AND(YEAR(TODAY()YEAR(A3)),( YEAR(TODAY())-YEAR(A3)<10),DAY(TODAY())<DAY(A3)),(((MONTH(TODAY( ))-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A"))))