I'm sorry for not giving enough info on the earlier post. I am so used
to seeing this that I made some assumptions that were incorrect. I do
apologize for that.
I am using an attendance tracking tool that assign's 1 point to an
associate if they miss work. Each associate can accumulate 12 points in
a year without serious consequence. We also give that associate an
avenue to reduce the number of accumulated points throughout the year.
The way it works is this. I receive 1 point for each occurence of
missing work. However, If I can go 90 days without another missed work
day that point will be removed from my total, and every 30 days after
the 90th day without missing work another point will be removed, or on
the 1 year anniversary of the 1st point it will be removed.
Example:
Jan 1st, I miss work and receive 1 point.
Jan 5th, I miss work again and receive a point.
Jan 15th, I miss work and receive another point.
I now have 3 points total.
April 15th - I have not missed another day since Jan 15th, so the point
I received on Jan 1st is removed. (90 day mark)
May 15th - I still have not missed any other days, so the point I
received on Jan 5th is removed. (30 day mark)
My attendance goes back and forth throughout the year and I receive and
drop points accordingly. I have now accumulated 11 points. Now I have
not quite made 90 days since the removal of my last point received, but
one of the points was given 365 days ago, so it now is dropped from my
attendance record regardless of the 90 and 30 day rule. Currentlly the
spreadsheet uses a countif statement to assign the point based on "S"
being entered in a cell on the date the associate misses work, and
then sums these points in a column.
Because our attendace system for acquiring and dropping points can get
confusing, some of our supervisors do not always keep up with this
accurately, so I thought since they track this in excel, then maybe it
could be done automatically. I thought I could use an IF statement to
check the current date, then remove a point if it meets one of the
requirements listed above. I was successful in using this
=IF(TODAY()A2+90,B6-B2) -- A2 is equal to the date of the first
point, B6 is the sum of all points accumulated, and B2 is the equal to
the point "1".
After that, I'm lost.
Thanks for your time and at least looking at my post.
Jolver
--
jolver
------------------------------------------------------------------------
jolver's Profile:
http://www.excelforum.com/member.php...o&userid=30462
View this thread:
http://www.excelforum.com/showthread...hreadid=501389