Trying To Create A Rolling Attendance System

[rrw11]

I'm working on creating a rolling attendance that will include a point system for certain instances. The points will last 1 year before they go away. Late Days (L)= 1 point. Unexcused Absences (UA) = 3 points. Early Leave (EL) = 1 point. I have everything set up and visually looking how I would like, except I'm having trouble developing a formula for the rolling points portion. I was able to create a formula that added up the correct amount of points for my entire spreadsheet, but I've been stuck on getting it to add up only a range of dates.


To sum it up, say it is January 10th, 2020. I'm trying to find the correct formula to add up the last year worth of points (January 10th, 2019 to January 10th, 2020). But then, when I open it up the next day, I need it to add up points from (January 11th, 2019 to January 11th, 2020) without me having to change anything. I tried playing around with the today function in different formulas to make it work it dynamically but could not get anything to come out. I was trying to get it to calculate to the main page sheet.


For example: an associate had 5 late days and 1 unexcused absence in the last year, so it would add up to (8 points). When you do attendance the next day, the formula updates itself and now in the last rolling year he lost 1 late days so now has (7 points).

I attached the excel sheet that I've been working on.

[dflak]

I knew what this spreadsheet would look like even before I opened it. It would have dates across the columns and years (actually I expected months) on separate sheets. This is an excellent layout for a final report. It is a particularly difficult layout to work with for data analysis.

Generally speaking, it is better to "go deep" rather than "go wide" with data. Meaning that the dates should be going down and very limited data going across.

The data should be organized in columns like:

Date | Associate | Item

where Item could be Late, Absent, Early Leave, Vacation Used.

Add events as they happen.

From this format, you can generate final reports that look like the one you presented but you can do all kinds of extra analysis mostly by using pivot tables that are easily implemented.

Also, you are doing a little mixing of apples and oranges with the shift information and starting balance for vacation. These items should be in a separate table.

[rrw11]

I did see one that worked in the format you are speaking about and figured that's what I'd have to end up doing. I was just hoping all that work didn't go to waste lol.

But I'll set it up in the format you advised above. Thanks for the help. I appreciate it.

[dflak]

Initially, it seems counter-intuitive but if you were born and raised in a database world, like I was, it makes sense. I should have mentioned, that this data should be in an Excel table. I see that you are already familiar with them.


You may have to add one more "field" (column) to the data: hours or points. What I would do with this field, is that when you select an item from a drop-down list, it would activate a user defined function. The function will do one of two things:

1. Prompt you for a number of hours if the item is vacation

2. Put in a formula that will compute the number of points if it is something else. The formula is "live" meaning that as dates go on, points will expire.

For vacation, you do want to track hours, so enter the number of hours in this column.

This will get you well into a system that you can use to do the tracking. Once you get it working, you can then "overlay" the data with user forms that will detect whether you entered vacation time in and prompt you to enter hours, or automatically enter a 1 for other events. This comes under the category of "finishing touches."

[dflak]

You forced me to do something that I was putting off for a while: https://www.excelforum.com/tips-and-...ml#post5042910. Thanks for the push.