I have a attendance tracker I downloaded from this site, but it does not have the rolling off as I need. Points need to roll off at 6 months (not 180 days).
Dates start at H6 and go right from there. There are unscheduled, late, early leave, and excused absences. Late and Leave early are worth .5.
This repeats for each category.
Please Login or Register to view this content.
I then have a total column that gives the total points.Please Login or Register to view this content.
Hi taylorsm.
Find these in the attached.
=TODAY() is in C1. This enables the formulas to get todays' date by reference rather than repeatedly invoking TODAY() in each cell. TODAY is volatile as well.
I also put an optional value in D1 for the number of months you want to "roll" back. It is referenced in the formula and give you some flexibility. If you'd prefer you can replace the reference to it in the formula below with a hard 6. I also used it to cross check my formula build.
In D4:G4 is a helper row that enables identifying the leave 'codes' by reference rather than hard coding them. It also permits you to use a single formula for all 4 columns.
Then this formula in D6:G19Formula:
Please Login or Register to view this content.
Dave
Yes!!!
Can you walk me through a little bit more of it for my understanding.
You are sumproduct(ing) anything that matches D4. Then you are multiplying that by anything that is greater than today's date -six months. ---- So this could return a zero, hence why that works right?
and you do it again, but this time you are multiplying by anything greater than or equal today's date - six months?
taylorsm,
Glad it works.
It is checking sequentially for values in D4, E4, F4, G4 in turn. Then it looks for corresponding dates that are both greater than today-6months and less than today. This produces an array of TRUE / FALSE. The math operators coerce these into their underlying numeric values 1 / 0. The net effect of multiplying 1s and 0s is an array of 1s and 0s which SUMPRODUCT sums up.
You can see the array by selecting this part of the formula in the formula bar
=SUMPRODUCT((D$4=$H6:$NH6)*($H$5:$NH$5>=EDATE($C$1,-$D$1))*($H$5:$NH$5<=$C$1))
(this assumes referencing the look-back months in $D$1) and hitting the F9 function key. You will see several 0s to start and likely have to scroll up in the formula bar several lines. You will eventually see some 1s if the codes are there in that date range. Other wise it will be all 0s.
Check this explanation out. The writer does a much better job of explaining the concepts than I will, (and it saves attempts to "reinvent the wheel")
http://xldynamic.com/source/xld.SUMPRODUCT.html
Last edited by FlameRetired; 10-26-2016 at 04:44 PM.
I love the setup, and plan on implementing it in my workplace as well. But I have a question: What if an employee both Arrives Late AND Leaves Early? Is there a way to incorporate that into the formula/drop down options in the table, so that it would still tabulate correctly?
I don't have that built in. It has not been an issue and we would just treat it as arrives late typically. Essentially anything under a full shift is half a point. I guess you could essentially create another item that was "ALLE" and assign it your point value.
Then my only question would be this: How do I update the values to implement just 2 new parameters, the AL/LE and NCNS (No Call/No Show), and add that into the formula? The AL/LE would just be 1 point altogether, and the NCNS would be 1.5. Normally, I can rewrite this stuff on my own, but I've been out of the "sheet development" side of Excel for long enough that this is starting to break my brain, in all honesty.
Try this. You can assign colors to the letters if you want. You can change the letters too.
taylorsm & FlameRetired:
Thank you, I managed to get it figured out and adjusted from the original spreadsheet without the edit you did, but I sincerely hope that helps somebody out in the future that finds it. It looks fantastic.
My next question is probably a bit unorthodox:
Is there a way to possibly create a forecast of employee behavior off of this data, such as a point total trend for the next 6 months?
I'm trying to think of a way to implement something like that to make our company's Point Tracker System somewhat proactive in coaching employees about their attendance behavior patterns/trends to help prevent the need for corrective action against them, instead of its current form, which is really just reactionary to the number of points they accrue over the given period of time.
Last edited by druchek; 12-11-2018 at 04:15 PM.
Hello druchek, and welcome to the forum.
You are welcome and glad to help.
Concerning your last question, I suggest starting your own thread making reference to this one. The topic is different enough to warrant it. As a new member you will not be able to post a link back to this one. (10 post minimum I believe.) What you can do is make reference to it by this thread's title and owner.
I hope this helps.
Dave
Question answered
Last edited by ltmcdonald; 04-06-2019 at 03:34 PM. Reason: Question answered
I am wondering what happens at the end of the year how do you keep going into the next year? Do you just have to keep adding on more columns at the end? But then wouldn't you have to go back and adjust your formulas to include those new columns. What is a xcel novice friendly way of setting it up to roll into the next year.
I have a file set up for a year of data entry Jan 1 to Dec 31 using the design and formulas from this thread that I adjusted to our point system. I also added the rolling 6 month formula to only look at occurrences from the last 6 months but being that it is a rolling calendar policy I need to set it up so that the scheduler can track into the new year and keep a 6 month look back. I want to make it as user friendly as possible without them having to add in any formulas etc.
Also, my profile says I work with 2010 Excel but I am creating this in and they will be working with the latest available version of Excel.
Last edited by binaandersen; 12-15-2019 at 09:31 PM. Reason: additional info
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks