IF it will work?

[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

[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.


"jolver" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> jolver
> ------------------------------------------------------------------------
> jolver's Profile:
> http://www.excelforum.com/member.php...o&userid=30462
> View this thread: http://www.excelforum.com/showthread...hreadid=501389
>

[Biff]

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

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

Biff

"jolver" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> jolver
> ------------------------------------------------------------------------
> jolver's Profile:
> http://www.excelforum.com/member.php...o&userid=30462
> View this thread: http://www.excelforum.com/showthread...hreadid=501389
>

[jolver]

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