IF it will work?

J

jolver

I am new to excel using "if statements", but I was told that I could
probably use excel to determine the total points assessed based on date
values. My rules are this:

90 days with no point = subtract oldest point
then every 30 subsequent days without a point = subtract next oldest
point
and/or subtract oldest point that was assessed 365 days ago.

Is this possible to do with the IF statement?

I posted earlier in the misc forum, but doesn't look as if anyone could
help there
 
B

Barb Reinhardt

Question:

90 days with no point - Subtract oldest point from what?

You haven't given enough information to be able to answer your question.
 
B

Biff

I posted earlier in the misc forum,
but doesn't look as if anyone could help there
Click to expand...

I think that's because nobody could understand what you were asking for! You
need to provide a better explanation.

Biff
 
J

jolver

I'm sorry for not giving enough info on the earlier post. I am so use
to seeing this that I made some assumptions that were incorrect. I d
apologize for that.

I am using an attendance tracking tool that assign's 1 point to a
associate if they miss work. Each associate can accumulate 12 points i
a year without serious consequence. We also give that associate a
avenue to reduce the number of accumulated points throughout the year
The way it works is this. I receive 1 point for each occurence o
missing work. However, If I can go 90 days without another missed wor
day that point will be removed from my total, and every 30 days afte
the 90th day without missing work another point will be removed, or o
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 poin
I received on Jan 1st is removed. (90 day mark)
May 15th - I still have not missed any other days, so the point
received on Jan 5th is removed. (30 day mark)

My attendance goes back and forth throughout the year and I receive an
drop points accordingly. I have now accumulated 11 points. Now I hav
not quite made 90 days since the removal of my last point received, bu
one of the points was given 365 days ago, so it now is dropped from m
attendance record regardless of the 90 and 30 day rule. Currentlly th
spreadsheet uses a countif statement to assign the point based on "S
being entered in a cell on the date the associate misses work, an
then sums these points in a column.

Because our attendace system for acquiring and dropping points can ge
confusing, some of our supervisors do not always keep up with thi
accurately, so I thought since they track this in excel, then maybe i
could be done automatically. I thought I could use an IF statement t
check the current date, then remove a point if it meets one of th
requirements listed above. I was successful in using thi
=IF(TODAY()>A2+90,B6-B2) --> A2 is equal to the date of the firs
point, B6 is the sum of all points accumulated, and B2 is the equal t
the point "1".

After that, I'm lost.

Thanks for your time and at least looking at my post.

Jolve
 
Top