Vacation Spreadsheet- formula not working.

[naiveprogrammer]

I need to track the accrued vacation time for each employee per their hire
date. I have a formula but it's not working. I need to track this per year,
not a total of all the years. Here is the criteria and formula-

*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)
*Starting on Jan 1 of the following date of hire = 80 hrs each year.
*Starting on Jan 1 of the 10th year of service = 120 hrs each year.

Formula for <=9 Years-

=IF(AND(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),AND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)>0,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),AND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)>=9),9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),AND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0))*80

=10 Years-

=IF(YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),AND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)>9,YEAR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),AND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120

 

[Chip Pearson]

You're asking someone to debug a mega formula without specifying
what the problem is. Does the formula return an error? An
incorrect result? What is the expected result, with what data,
and what is the actual result? You need to provide lots of detail
about the nature of the problem, rather than simply saying "this
massive formula doesn't work".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"naiveprogrammer" <[email protected]>
wrote in message

 

[naiveprogrammer]

Sorry. The massive formula returns an incorrect result. For <=9 Years
formula I should get a result of 80, rather I receive 720. For >=10 Years I
should get 120, rather I get 600.

Hope this helps!

 

[Jim Thomlinson]

Without any source data there is no way for us to effectively check why you
are getting the result you are. My suggestion would be to break the formula
up into a bunch of intermidiate values (Calculate < 9 and >10 and ... in
seperate columns and ensure that each cell is returning what it should. Then
use a formula based on if statements and the intermediate values to arrive at
the correct totals. Sometimes formulas get too big to effectively be debugged
when something goes wrong.