I have a payroll spreadsheet that has Office hours and a transportation pay scale on the same page it works well.
the transportation dept. has a safety program.
the program starts after 1 year service.
Cell J1 has the hire date of the employee.
the safety bonus starts @ .005 and goes tp.03 by the quarter.
if the driver has an accident the bonus starts over @.005
there is a question to be answered @ cell N2, Has this driver had an accident since last Quarter Yes = Y No = N.
when y is in the cell there are no problems but when N is in the cell has this problem.
Here is the formula That works well till I get within 1 year and 3 months of current date.
IF(N2="N",I25*0.005,MIN((INT(DATEDIF(DATE(YEAR(J1)+1,MONTH(J1),DAY(J1)),TODAY(),"m")/3)+1)*0.005,0.03)*I25)
then it returns #NUM! (I can't get that to be blank untill the bonus kicks in.)
I need the quarters to work from the first of the year and not the hire date.
but the hire still dictates when the bonus kicks in.
this has got me stumped.
Is there any IDEAS?
Bookmarks