# Subract Cell if Older Than a Year With Other Conditions

1. ## Subract Cell if Older Than a Year With Other Conditions

I am setting up a workbook for our absence reporting for work and have everything figure out except one thing. I have it where it subtracts points if the points were given over a year ago and also have it to subtract one point for every 90 days of perfect attendance. The problem however is if someone gets 5 points for a no show on January 1, 2013 and then don't have another reportable until June 01, 2014, it subtracts the five points and then an additional 1 since they had over 90 days of perfect attendence and it also goes into negative when employees cannot have negative attendence points. Is there a way to keep the extra point from being deducted and keep it from going into negative? Below is my workbook and the formulas used.

Absence Reporting.xlsx

2. ## Re: Subract Cell if Older Than a Year With Other Conditions

OK. You have one Horrible Equation (HE) there. However, this seems to meet your criterion (it's now 3x longer, too - Bound To Impress). Basically, it's:

If HE<0, Put HE+1, Otherwise, HE.

BtW, there's an error in your Conditional Formatting. If you change I 11 to NS, the column with the HE goes yeppow. In CF change the equation to read \$c11 instead of \$c\$11 and it'll be OK.

3. ## Re: Subract Cell if Older Than a Year With Other Conditions

One more thing that I caught while inputing data. A point is suppose to be deducted for every 90 days of perfect attendence but if they only have a 0.5 point occurence (Tardy or Missed Punch) than subtracting 1 point will be taking more away than needed to. Is there a way to keep it from taking a full point for those two occurences and making it only subtract 0.5? So if D11 shows 01/01/14 and F11 is a T (Tardy) and the next occurence isn't until 06/06/14 (G11) and that was a NS (No Show). Right now it subtracts a full point instead of just the 0.5 given due to the Tardy.

4. ## Re: Subract Cell if Older Than a Year With Other Conditions

Probably can be done, but I'd really need to try to figure out your HE... The only question that springs to my mind is why are you rewarding people who take MORE time off (minus 1 point) while penalising those who are merely tardy (0.5 points off). So, if someone's going to be a bit late, they might as well score a full point - they'll be forgiven by "the system" just as quickly...

5. ## Re: Subract Cell if Older Than a Year With Other Conditions

I agree but that was a decision made by our Human Resource department. I have been working on this workbook for over a week now and can't seem to figure it out. I think I've put too much time into that I cannot seem to think straight. I appreciate your help.

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

#### 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