Software

General discussion

Gravatar
Locked

Excel Formula

By jprogrammer ·
Excel Formula not working...
I hope this is where I post this question?

I have a MASSIVE formula that I using to accrue vacation time based on different criteria. Basically I am wanting to use the same formula that I'm using now, just add something to it. I can not get it to work however.

My problem- I need to execute a formula for an employee only IF the employee has worked 140 hours for the month. I can download the employee's hours from an ODBC so that is no problem. My issue is how do I say in the formula- =IF (A3 >= 140)THEN,


massive 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)),(((MO NTH(TODAY())-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A"))))
--------------------------------------------------

THANKS for any help on this!!!

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
+ Follow this Discussion ·
| Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by chitosunday In reply to Excel Formula

=IF (A3 >= 140,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)),(((M O NTH(TODAY())-1)*(80/12))+((12-MONTH(A3))*3.34)+((YEAR(TODAY())-(YEAR(A3)+1))*80)),"N/A")))),"")

gravatar
Collapse -

by jprogrammer In reply to Excel Formula

Thanks! Yet now my massive formula isn't working. Instead of getting their actual accrued hours, I'm getting all "N/A"'s. Maybe this will help- This formula is for employees who have worked 1 month-9 yrs. So the formula is based off of hire date and then accrued at 3.34hrs per month. The most anyone can have though is 40 hrs.(actually 40.08=3.34*12) But I only need the formula to run if they have worked at least 140 hours the prior month. I download the prior months hours worked from an ODBC source so I don't have to actually figure this up thank goodness!

I hope this makes sense?

gravatar
Collapse -

by chitosunday In reply to Excel Formula

Your cell should separate the date hours rendered in prev month and the date hired. In your example, your cell is only a3. So assuming you have cell a3 as hours rendered and cell a4 as
date hired the formula (you need to install analysis toolpack in your addin for this formula to work)
=IF(A3>=140,MIN(3.34*12,DATEDIF(A4,A9,"m"))*3.34,"n/a")

gravatar
Collapse -

by anikmankar In reply to Excel Formula

To make the logic more readable create a function and passparameters to it. This way it can be more structured and easy to maintain.

Back to Software Forum
4 total posts (Page 1 of 1)  

Start or search

Related Discussions

Related Forums