+ Reply to Thread
Results 1 to 9 of 9

DATEIF Function

  1. #1
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    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?

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote 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....
    Last edited by daddylonglegs; 08-12-2008 at 07:26 PM.

  3. #3
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    Dateif

    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

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    This formula should do the first part.

    Adds 0.005 after exactly one year, then another 0.005 every quarter start date after that, up to a maximum of 0.03

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

    Now you want that to change when an accident occurs. Let's assume you have a cell with the date of the last accident (it would be blank if there haven't been any).

    You said elsewhere that the bonus wouldn't start again until 2 quarters after the accident, does that mean it would set back to zero or 0.005? When would it start to move back up, exactly 2 months after the accident or on the first quarter start date after that?

  5. #5
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    Thumbs up Dateif

    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.

  6. #6
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    Dateif

    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

  7. #7
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    Help To Finish This DATE IF Formula

    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!
    Last edited by Get_Involved; 08-17-2008 at 12:48 PM.

  8. #8
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10
    (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

  9. #9
    Registered User
    Join Date
    08-12-2008
    Location
    Georgia
    Posts
    10

    Bump

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1