Excel formula for Multiple Date Scenario

[leahmullins]

I am needing a formula to calculate attendance depending on the employee's time of service. If an employee has worked for the company less than two years, attendance is calculated in a way the employee is allowed to miss less days (only allowed 3) before receiving a point and if they have been with the company longer than two years, it is calculated that the employee is allowed more days to miss (allowed 5) before receiving a point. I want the formula to determine which way to calculate it based on the date of hire I enter into the spreadsheet. I currently have this formula: =IF(OR(K8<=3,K8<=4),K8*0,IF(F3>TODAY()-731,(K8-4)*1,(K8-5)*1)), but it won't do the correct calculation based on the hire date when the days missed is >=4 if they have been with the company less than 2 years. When K8=4 it won't calculate a point. If I manipulate the sum "(K8-4)*1 by adding 1 ((K8-4)*1+1), it throws off the calculation if the employee has been with the company over 2 years (it will calculate -1 if they have missed 4 days). A couple cells to know: F3= the person's Date of Hire; K8=the amount of days missed. I am currently using Excel 2013 with Windows 7 Professional. Any help would be greatly appreciated.

 

[DRSteele]

Welcome to the Forum.

If you provide us some sample data together with expected results, we could conduct experiments.

 

[leahmullins]

Thank you for getting back. So, a couple examples would be an employee has a DOH of 4/17/2017 (cell F3). He has missed 4 (cell K8) times. He should receive "1" point because he has been with the company less than 2 years. If the formula works as I would like it to, the cell this formula is in would populate "1". If the employee had missed 6 times, he should have "3" points which once again should populate in the cell with the formula. Another scenario would be if an employee's DOH is 5/12/2005 (cell F3) and he has missed 4 days (cell K8). He wouldn't get a point because he has been with the company longer than 2 years, so the cell with the formula should have "0" in it. If the employee misses 5 or 6 times, he should have "0" or "1" point respectively, once again populated in the cell with the formula.

 

[leahmullins]

Thank you for getting back. So, a couple examples would be an employee has a DOH of 4/17/2017 (cell F3). He has missed 4 (cell K8) times. He should receive "1" point because he has been with the company less than 2 years. If the formula works as I would like it to, the cell this formula is in would populate "1". If the employee had missed 6 times, he should have "3" points which once again should populate in the cell with the formula. Another scenario would be if an employee's DOH is 5/12/2005 (cell F3) and he has missed 4 days (cell K8). He wouldn't get a point because he has been with the company longer than 2 years, so the cell with the formula should have "0" in it. If the employee misses 5 or 6 times, he should have "0" or "1" point respectively, once again populated in the cell with the formula.