This formula is to multiply days worked times a variable to establish paid time off earned.
AF30 cell is the arbitrary entered date, often today
AQ1 is hire date
V31 daily accrual rate if less than 5 years with the company
V32 daily accrual rate if more than 5 years but less than 10
V33 daily accrual rate if more than 10 years
problem I've found is if the employee has been with the company less than a year, it needs to calculate the amount of days worked times the daily accrual rate.
i.e. 8/15/17 worked till today (11/22/17) should yield 99 days times the value in V31 yielding 21.69783 hours not 71 hours, as if the employee worked all year.
=IF(ROUND((AF30-DATEVALUE("1/1/2017"))*IF(AF30>DATE(YEAR(AQ1)+10,MONTH(AQ1),DAY(AQ1)),V33,IF(AF30>DATE(YEAR(AQ1)+5,MONTH(AQ1),DAY(AQ1)),V32,V31)),0)<0,0,ROUND((AF30-DATEVALUE("1/1/2017"))*IF(AF30>DATE(YEAR(AQ1)+10,MONTH(AQ1),DAY(AQ1)),V33,IF(AF30>DATE(YEAR(AQ1)+5,MONTH(AQ1),DAY(AQ1)),V32,V31)),0))
tired to add =DATEDIF(AQ1,AF30,"d") to the final equation; but yeah .... it didn't work.
I'm stuck on this doozy.
Help please
Bookmarks