Attendance points tracker for rolling year.

[oldschool08096]

Hello first time posting here!
Im currently tasked with creating an attendance sheet that will track an employees accumulated points. I need this sheet to go on for as long as they work for the company so for the life of the career here. This also means this will be for a single employee. What I have currently done is Cell A13 has a date of an occurrence ( 10/5/19 ), Cell C13 has a reason code for the occurrence ( T ). Cell E13 has the points from said occurrence auto filled with this code. =IF(C13= "T",0.5,IF(C13= "A",1,IF(C13= "N",2,IF(C13= "S",0,IF(C13= "E",1,IF(C13= "B",0.5,0))))))

That way I can just add T in for Tardy and it adds .5 in E13. What im trying to do now is have F13 take the entered date in A13 and after 365 days a rolling year has passed take the points accumulated in E13 and make it read as 0 or erase the points all together. Other words show the points for the occurrence active until the 365 days the rolling year has passed. That way when I have H13 count all the points per rolling year I know in a flash how many points total they currently have every time I look at the spread sheet.

Any help on this would be GLADLY accepted as I have been pulling my hair out trying to make this work.

[jme1013]

to summarize...

You're looking to have a never ending sheet that always looks backwards 365 days to show the amount of current points accumulated due to occurrences?

It's helpful if you post an example workbook of what you have so we don't try to recreate what you've already done.

The only time you input something in this sheet is when they have an occurrence?

[oldschool08096]

That is correct, no occurance no need to do anything. All the code i have is what i have added above.

[AliGW]

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[jme1013]

Try this in E13...

=IF(TODAY()-A13>365,"",IF(C13= "T",0.5,IF(C13= "A",1,IF(C13= "N",2,IF(C13= "S",0,IF(C13= "E",1,IF(C13= "B",0.5,0)))))))

this will only populate points if its been within the last 365 days. So as the days go along, when an occurrence becomes older than 365 days, those points will disappear.

I'd just make a cell at the top that sums column E:E so you always have the total of active points at the top.

[oldschool08096]

jme1013 Thank you, this is just what I was looking to do.

[jme1013]

Perfect, Glad to hear it!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.