Feb 12 2023 11:13 PM
Hi All,
We track retention status of each employee in a excel sheet. Time to time this retention status changes based on latest update. So a particular cell value changes time to time.
Can we track these changes and add those records against the employee monthly?, in a separate sheet? For example Employee A Jan 23 Status, Feb 23 Status. With this I can develop a historical track of his retention status.
Seeking for a guidance in building this. Thanks in advance.
Feb 13 2023 07:46 AM
Can we track these changes and add those records against the employee monthly?, in a separate sheet?
Of course. Exactly how, what method, etc., will depend to a great extent on how you organize and keep your data already on other aspects of employee history.
Is it possible for you to create a mockup file -- no real names or other identifications -- that gives us an idea of your starting point on computerized records. Post a copy of that file on OneDrive or GoogleDrive and paste a link here granting edit access.
Feb 13 2023 09:18 AM
Feb 13 2023 09:34 AM
@NaradaJay95 I was responding as the former Director of the HR/Payroll database for a major US corporation. @Patrick2788 gave you a feature of Excel that tracks changes to a spreadsheet. If that's what you're seeking, that's fine by me.
My reading of your question, though, comes from my own background: I was assuming you were really seeking the most effective ways to keep what would amount to "Employee History," specifically in connection with "retention status".....but quite possible Employee History as it applies to all kinds of status changes, from hires to promotions, to salary changes, through to termination or retirement. That's also why I asked that you post a copy (without real names) of what you have now in place as an employee database. There's no reason, I assume, for us to design something "from scratch."
Feb 13 2023 07:08 PM - edited Feb 13 2023 07:58 PM
Emp ID | Employee Name | Current Risk Status - Retention |
1 | Employee A | No Known Risk |
2 | Employee B | Low Risk - 1 - 2 year |
3 | Employee C | Medium Risk - Within 1 year |
4 | Employee D | High Risk - Immediate |
Current Risk Status changes time to time based on the latest update of the risk. For example Employee A can be in "No Known Risk" in the month of Jan and then changed to "Low Risk - 1 - 2 year" in the month of Feb. So I need to track those changed monthly in a table like below.
Emp ID | Employee Name | Jan-23 | Feb-23 |
1 | Employee A | No Known Risk | Low Risk - 1 - 2 year |
Feb 14 2023 06:13 AM - edited Feb 14 2023 06:13 AM
You need (or at least it would be greatly preferable) to give a bit more information here. What you're displaying is what I'd call OUTPUT from a tracking system. You have two output reports,
What records do you have, currently, that would enable the production of those reports? What kind of database exists already of your employees? Or is there none, and that's what you're seeking?
What's the INPUT that can serve as the basis for the database on which these two reports could be generated? Said another way, how do you determine--what happens that makes a change, how do you learn about it, do you learn after the fact of the change from the employee or is it something determined by outside agencies, etc. etc.
These questions may not make sense, I suppose. But unless we have the answers, unless we know what the overall process is, from event to input to database to report, then I'm not sure what help Excel could be other than giving you rows and columns to display what you show.
Now if you're saying that the first table in your posting is the INPUT and Database, rolled into one, then what you need to do is modify it to include the date that that status changes,
Date | Emp ID | Employee Name | Current Risk Status - Retention |
1/2/23 | 1 | Employee A | No Known Risk |
1/4/23 | 2 | Employee B | Low Risk - 1 - 2 year |
1/15/23 | 3 | Employee C | Medium Risk - Within 1 year |
2/1/23 | 1 | Employee A | Low Risk - 1 - 2 year |
1/31/23 | 4 | Employee D | High Risk - Immediate |
But I want to go back to my questions regarding the INPUT: please describe what determines that status in the first place, how it comes about, who determines it, when you are informed, etc [And just for context, what are we talking about anyway? COVID risk? What? What is "retention"?] And, as I asked before, what other records do you keep on employee status? Or are you not involved in this from a personnel/HR kind of role (where people would track pay class, pay rate, employment date, promotion dates, etc?).
Again, the reasons for these questions: you're not keeping these records on risk/retention in a vacuum: it helps in designing a process to know the bigger context vis-a-vis records that are kept.