Attendance Tracker

**bsoper** · 02-11-2010, 03:20 PM

Hi everyone,

I have been tasked with coming up with an attendance tracker at work. I have been messing with it for a while now and I have been surfing the web for answers. I have found a lot of things that are similar, but I can't quite adapt them to what I want to do.

So, we award 1/3 of a point for a tardy and 1 point for an absence.

The Tardy/Absence criteria will pull from another spreadsheet.

I used a countif to determine the number of points based on Tardy/Absent.

I am now trying to create a rolling 90 day total and a rolling 365 day total. So I will pull up the file, add a new column that will pull the previous day's Absences/Tardies and then have my 90/365 day period update to reflect the new date.

I have been messing around with OFFSET and EDATE, which are new to me. If anyone can help me come up with a formula to figure this out, I would be ecstatic! It would be even better if you could explain how it was done as well so I can learn. Thanks!

~B

**DonkeyOte** · 02-11-2010, 03:46 PM

Using your sample:

Please Login or Register  to view this content.

you could use OFFSET but unlike INDEX construct above it's Volatile so I prefer the INDEX route myself... up to you of course...

**bsoper** · 02-11-2010, 04:01 PM

When I add in a new column, what changes will I need to make to the forumla? I assume moving the COLUMN reference one column over? I haven't used that function before.

The more I learn about Excel, the less I feel I know...

**DonkeyOte** · 02-11-2010, 04:04 PM

The function is designed such that you won't have to change anything... assuming you're always inserting the new column before the calc columns of course

(it works on the assumption that the most recent data is always the column immediately to the left of the calculation columns themselves)

If you want to test - change 90 to a much smaller value - say 5 and begin inserting columns - that should help you validate that it's working to your specification(s).

Post back and let us know either way.

**bsoper** · 02-11-2010, 04:33 PM

Ok, I got it. It's working fine, Thanks!

**overa32182** · 10-06-2015, 06:19 PM

I'm very very new at Excel but and this formula is excellent but how can I have this formula calculate Absent =2 Tardy = 1 and Early Out = 1? Thanks for any help anyone can provide.

**protonLeah** · 10-06-2015, 09:56 PM

overa32182,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.