PDA

View Full Version : Attendance Spreadsheet - Custom point reduction



formulation
09-19-2017, 07:16 AM
I've attached a copy of sample date and disabled a macro I have to go to the current date in row 5.
20396
I've spent a decent amount of time on this spreadsheet to total attendance based on a few variables. I've been stuck for a month on the last feature.
I have set up a situation in the sheet and explained it below, that should hopefully explain the issue better once you take a look.

For every 90 calendar days of perfect attendance (blank cells) = One point will be removed from the total points column for each person.
*However, if an employee's total points is at 0 at any given time, blank cells(perfect attendance) should be ignored. 0 points is the minimum.

Rolling 365 day point drop off period (working) (Uses cell ADO:6)
Points only count on or past the date of hire (working) (Cell ADN:6)
Each cell has a drop down with selections for tardy (TD), call off (CO), Leave Early (LE), Continuous Absence (CA).
TD = .5 Points |||| CO = 1 Point |||| LE/CA can be ignored (0 points)

Currently everything is working as intended except for one feature, the 90 day perfect attendance reward.
Weekends are excluded from the dates in row 5 so anytime I refer to 90 days it's 65 cells. (not perfect but it works for our use)

I set up a situation in the above file that shows the problem. In row 6, they receive a call off (CO) on 1/3/2017 (1 point)
Then they have two 90 day periods of blank cells. Followed by a call off on 8/15/2017.
This should result in 1 point instead of 2 points.

1/3/2017 = 1 point First 90 day period of blank cells = 0 points Now that employee has 0 points, the second 90 day blank cell period should be ignored as they can't go below 0. 8/15/2017 call off should bring total back to 1 point.
*other rules in place already... Any cells with a drop down selection before their date of hire are ignored.
If they have 0 points and then (5) 90 day perfect attendance periods in a row, then a call off, the total should be 1 point.https://www.excelforum.com/images/misc/paperclip.png Attached Files

SamT
09-19-2017, 02:48 PM
Rewarding

If Points > 1 then
Points = Points - 1
ElseIf Points <= 1 then Points = 0

90 days/65 cells no points

If WorksheetFunction.CountA(Cell.Offset(, -64).Resize(, 65)) = 0 Then Reward.

formulation
09-20-2017, 08:55 AM
Excuse my ignorance. I need help getting this to work correctly.

I'm right clicking my workbook, insert>module, then what? I tried a few methods I found online but still receiving errors when I try to run the module.

I'm assuming something I have entered is wrong or do these need to be in two separate modules?

Sub Reward()

If Points > 1 Then
Points = Points - 1
ElseIf Points <= 1 Then Points = 0


If WorksheetFunction.CountA(Cell.Offset(, -64).Resize(, 65)) = 0 Then Reward


End Sub

SamT
09-20-2017, 12:05 PM
I would probably put that in the UserForm Module, since I suspect that the Value of "Points" comes from there.

Any reference to a Cell or Range must also include a reference to the Worksheet.

For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.

Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp)
If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward


Once a Range Variable is Set to a particular Range, the Sheet reference is inherent in the Variable.

BTW, Your attachment didn't. Attach, that is.

formulation
09-20-2017, 12:56 PM
In line two of my original post is the attachment. It works for me?

I'm getting a bit lost when it comes to setting a range variable. I'm following a few guides after googling setting range variables vba excel with no luck. :dunno
20413

I would probably put that in the UserForm Module, since I suspect that the Value of "Points" comes from there.

Any reference to a Cell or Range must also include a reference to the Worksheet.

For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.

Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp)
If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward


Once a Range Variable is Set to a particular Range, the Sheet reference is inherent in the Variable.

BTW, Your attachment didn't. Attach, that is.

SamT
09-20-2017, 03:24 PM
Line 2??? I was looking at this line

.https://www.excelforum.com/images/misc/paperclip.png Attached Files

OK, I DLed it... Checking

SamT
09-20-2017, 03:37 PM
For Example. Note Cel is a Variable, but Cells() is an Excel Range Keyword.
Set Cel = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp)
If WorksheetFunction.CountA(Cel.Offset(, -64).Resize(, 65)) = 0 Then Cel.Value = Reward



My bad. that was not a well thought out example. After setting Cel to a Cell in Column D, the next line tries to reference the Cell 64 columns to the left of D

My Excel XP can't use the Functions in your attachment, so I can't see what your Worksheet is doing. I'm pretty sure I see what you want it to do, But I don't see how you are trying to do it.

Don't go away, Several experts are watching. One will be here shortly.

formulation
09-21-2017, 07:56 AM
Updated sample data (Referenced cell data was incorrect)20423

formulation
09-26-2017, 07:01 AM
Anyone have an idea? If you have time to figure it out use the sample data in post #8 above this.
Thanks in advance!