+ Reply to Thread
Results 1 to 5 of 5

Subract Cell if Older Than a Year With Other Conditions

  1. #1
    Registered User
    Join Date
    12-13-2012
    Location
    Wichita, KS
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,938

    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.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    12-13-2012
    Location
    Wichita, KS
    MS-Off Ver
    Excel 2007
    Posts
    5

    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. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,938

    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. #5
    Registered User
    Join Date
    12-13-2012
    Location
    Wichita, KS
    MS-Off Ver
    Excel 2007
    Posts
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to subract cell by fill color
    By HPIMICHAEL02 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-31-2013, 03:13 AM
  2. How to clear cell content older than a year (or more)
    By myexcelstar in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-13-2013, 03:34 PM
  3. Creating putton to add (or subract) a fixed value from a cell
    By hawkchev in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2008, 02:51 AM
  4. add and subract in one sum
    By k1ngy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2006, 05:10 PM
  5. Converting Older Dates to the Current Year
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-23-2005, 12:00 PM

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