Attendence Tracker - Dynamic counts

[madhu4excel]

Hi,

I have prepared an excel tracker with 3 sheets. one sheet for entering points for each employee (1 or 0.5). One point for employee should drop off if they did not have any point in 90 consecutive days; again same should be done for 365 days. These calculations are done in "summary" sheet. The quarterly results of points and suggested penalty are displayed in Report sheet.

Recently the requirement has changed. What is required is the 90 days count should start from the 1st point an employee gets( rather than from starting of month). This should roll over, count accordingly and display as needed. I am very confused to get any luck on this as this varies for each employee. Please help me. Attached my excel file here.

[mikeTRON]

So what exactly are you trying to accomplish?

[gak67]

Would something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

work? You could use an offset/match to change the row number to add up. Your data also only goes to 31 October 2013, so you could use that instead of NOW().

[madhu4excel]

Hi gak67

Thanks. When I use this formula, it is showing 0 for everything. can you explain more clearly. What I need is couting the points gotten in 90 days for each employee( where the count should start from the day when they get first point) .

[madhu4excel]

So what exactly are you trying to accomplish?

What I need is couting the points gotten in 90 days for each employee( where the count should start from the day when they get first point) . pls help

[madhu4excel]

Would something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

work? You could use an offset/match to change the row number to add up. Your data also only goes to 31 October 2013, so you could use that instead of NOW().

Thanks. When I use this formula, it is showing 0 for everything. can you explain more clearly. What I need is couting the points gotten in 90 days for each employee( where the count should start from the day when they get first point) .

[gak67]

A couple of things. You need to be careful with Excel people with using "count". From what you are saying I think you want to sum the points. To us, "count" means to count the number of times a person got points, not to sum up the points they got.

I am confused about the 90 days thing (my formulas were for looking at the the last 365 days). If they go 90 days without getting any points their points balance goes down by one point? Also, do points never expire?

[madhu4excel]

A couple of things. You need to be careful with Excel people with using "count". From what you are saying I think you want to sum the points. To us, "count" means to count the number of times a person got points, not to sum up the points they got.

I am confused about the 90 days thing (my formulas were for looking at the the last 365 days). If they go 90 days without getting any points their points balance goes down by one point? Also, do points never expire?

what you understood is perfect about the 90 days concept. and points won't expire as we have to do for rolling years.

[madhu4excel]

A couple of things. You need to be careful with Excel people with using "count". From what you are saying I think you want to sum the points. To us, "count" means to count the number of times a person got points, not to sum up the points they got.

I am confused about the 90 days thing (my formulas were for looking at the the last 365 days). If they go 90 days without getting any points their points balance goes down by one point? Also, do points never expire?

Thanks for making me understand about the count and sum issue. hope i gave the problem clearly now