Tracking Points that expire

Hi Everybody,

I am trying to track points for HR Department in a simple way. The rules are as follow:
- points expire after 1 year from the time was aquired.
- every 6 months employee receive one wellness day based on perfect attendance - so I have to count point in a specific 6 month period
- every quorter employees receive bonuses calculated based on their points (in a particular quorter/period) - again I need to be able to add the valid points in this period

So far I thought of having one column with names, then entering the date the point was issued and the next cell the number of points and continue alternating date/points. I would make a total using in may case =SUMPRODUCT(MOD(COLUMN(B2:U2),2),B2:U2) that would add only the points and skip the cells that have the date.

Problems: - how do I exclude the points that have expired and sum up? I know I can have a sum if the date is less than 1 yera from today, but how can I do it for every cell that has the date?

Also how can I put the condition for the dates to be in a certain range, taking in consideration that is every other cell?

Or, is there an easier way to keep track and have all employees in one place?

By now they kept a spreadsheet for each employee and it was a hasle to calculate bonuses and keep track.

I would appreciate any imput.

Thank you!

Yes, is definitely something that would work. I can’t thank you enough for creating this for me.

For my own knowledge, how or where exactly are you defining ColA and ColB in the way you explained above?

Also, for calculating bonus purpose, what would be the easiest to calculate the valid points in a quarter? So far I moved the totals on a second spreadsheet and apply a filter. I would sort the employees and select the range period that I would need. Still I wouldn’t have the totals. I would have to do that separate. Is it any way that I can have totals the same as you listed for valid points, but defining a certain period?

Thank you again for the time and effort you put in!

tigeravatar example for Amber12 (2).xlsx I explained in the attachment and gave an example of what I would have to calculate. Hope I explained well.

Thank you!

You are a GENIUS! Thank you so much. These days I will actually apply to my real case at work, but I think it will work and is something that I was looking for. I must admit I didn’t work before with these function, even if I’m pretty good with excel.

For future I should be able to just change the dates for the beginning and end of the quarter based on the period that I need, correct?

Oh, I am sooooo happy and so grateful for your help.

Thank you a million times!

HELP!

A few weeks ago I added some columns and changed some information and I managed to adjust the formulas. Now I see all these errors and I don't undersatnd why. Please help me to see where the error is and what can I do when I add more columns with additional information.

Thank you

I thought that the header could be an issue, but I ignored it.
Thank you soooo much.