DATEIF Function

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?

Originally Posted by Get_Involved

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.

Let me give an example and see if I understand......

Assume hire date is 5th November 2006

I assume that there is no bonus until 5th November 2007, on which date bonus starts at 0.005*I25 and then rises by 0.03*I25 every 1st Jan, 1st Apr etc.

If so try

=IF(DATEDIF(J1,TODAY(),"y"),MAX(INT(DATEDIF(DATE(YEAR(J1)+1,FLOOR(MONTH(J1)-1,3)+1,1),TODAY(),"m")/3)*0.03*(N2<>"Y")+0.005,0.005),0)*I25

If that doesn't do it then please give some sample hire dates and the results you expect

Edit: some of your description seems to be at odds with the formula you give, e.g. you say that bonus starts at 0.005 and goes up by 0.03 a quarter but formula seems to suggest the reverse...and it would make more sense, I think, the other way round, if so then transpose 0.005 and 0.03 values in my formula....

Daddykonglegs
that formula does work. but this is what is needed.
it takes 6 (1/4)s to get to the .03 from .005 (.005 cents at a time).
1st quarter .005 2nd quater .01 3rd quarter .015 ect.
once it gets to .03 it dosn't go any higher.
If I put Y in the cell N2 the bonus stops and then starts at .005 and works its way back up to .03 when I put N back.
Just thought of something has to make it start back .005 not the .03 because the accident not the hire date.
OOPS I think I'm getting into deep looks like this is not going to work.
Thanks

I inserted *(N2<>"y")*0.005+0,0.03),0)*I25 back in and changed the .005 to 0 and it works perfect for the year. Thaks for that.

Actually it is (1/4) no bonus and then starts back on the (2nd 1/4) after the accident at 0 again and works back up by the quarter.
I see already there has to be another cell for the accident date unless you know another peice to add in the formula.

Back in 1997 I had an running total but forgot how to write the formula,that sure would be nice at the bottom of the sheet
it is a byweekly sheet.

Here is what we have that works perffect for the 6 calander quarters after the 1 year hire date to max the .03 bonus.

=IF(DATEDIF(J1,TODAY(),"y"),MIN(INT(DATEDIF(DATE(YEAR(J1)+1,FLOOR(MONTH(J1)-1,3)+1,0),TODAY(),"m")/3)*(K3<>"y")*0.005+0,0.03),0)*I27
When I put the letter Y at K3 the bonus turn blank (PERFECT).

I have moved the hire date cell to K3 to make room for the accident date.
That cell is O3. This will be the date that is used to figure the bonus by untill the next accident date is inserted.


From the accident date one calendar quarter needs to pass before the bonus starts at .005 and goes up to .03 by the quarter.
I have tried to make this formula start working from the accident Date O3.
no matter what I do it keeps returning (#NUM!). I know I am missing something.

This one has made my fuse to my brain not blow but Glow had to back off.
That didn't work.
Thanks

I have put MAX(O1:O3) In cell O4. (what I'll do here is make the font white so the user can't see it.)
I can,t get my formula to skip one quarter before it starts with the bonus using cell O4.

Based off the "y" at Cell K3
almost had it but then it went to #NUM!

(1st Part)
=IF(DATEDIF(T4,TODAY(),"y"),MIN(INT(DATEDIF(DATE(YEAR(T4)+1,FLOOR(MONTH(T4)-1,1)+1,0),TODAY(),"m")/3)*(K3<>"y")*0.005+0,0.03),0)*T11
Cell T11 Is 1000 (miles)
Cell K3 (is the answer to a question, has the driver had an accident in the past Quarter?) Answer (Y for yes or N for no)
Cell T4
Hire Date---> 1/1/2003
Safety Bonus Starts After 1 Year Service
If the answer is N the bonus starts, If the answer is Y there is no bonus.
This Part works perfect
_______________________________________________________________
(2nd Part)

Using the formula above!
There is another cell that has the date of the last accident.
Cell T5.
1/1/2008

It is at this point this quarter does not count because of the accident. (1/1/2008 through 3/31/2008)
There will be no bonus for the quarter 4/1/2008 through 6/31/2008.
Then the formula starts adding the bonus starting with .005 and raising .005 percent till it reaches .03 percent by the quarter until the next accident.
This part I keep getting a #NUM! Or a #Value

Any suggestions will be helpfull
Thanks

I am Got to get this to work on the 2nd part