Record the cell changes

Copper Contributor

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. 

5 Replies

@NaradaJay95 

 

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.

@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 IDEmployee NameCurrent Risk Status - Retention
1Employee ANo Known Risk
2Employee BLow Risk - 1 - 2 year
3Employee CMedium Risk - Within 1 year
4Employee DHigh 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 IDEmployee NameJan-23Feb-23
1Employee ANo Known RiskLow Risk - 1 - 2 year

@NaradaJay95 

 

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, 

DateEmp IDEmployee NameCurrent Risk Status - Retention
1/2/231Employee ANo Known Risk
1/4/232Employee BLow Risk - 1 - 2 year
1/15/233Employee CMedium Risk - Within 1 year
2/1/231Employee ALow Risk - 1 - 2 year
1/31/234Employee DHigh 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.