Forum Discussion
Record the cell changes
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."
mathetes .
| 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 |
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,
- the first one showing the current status of all employees,
- the second showing the history of a single employee.
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.