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(A3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(TODAY())=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"))))

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(A3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(TODAY())=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"))))

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(A3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(TODAY())=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"))))

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

"naiveprogrammer" <[email protected]> wrote in
message news:[email protected]...
> 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(A3)),(MONTH(TODAY())-MONTH(A3))*3.34,IF(AND(DAY(TODAY())<DAY(A3),YEAR(TODAY())=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"))))