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"
wrote in message
...
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)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)0,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(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)),A ND(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)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)9,YE AR(TODAY())-YEAR(DATE(YEAR(A2)+1,1,1))-IF(OR(MONTH(TODAY())<MONTH(DATE(YEAR(A2)+1,1,1)),A ND(MONTH(TODAY())=MONTH(DATE(YEAR(A2)+1,1,1)),
DAY(TODAY())<DAY(DATE(YEAR(A2)+1,1,1)))),1,0)-9,0)*120