Forum Discussion
Time stamp when a cell is changed
Unfortunately for you, NOW() means exactly what it says, NOW! And NOW! changes to whatever NOW happens to be, not based on other cell changes.
So why don't you back up and give us the bigger picture of what your spreadsheet is capturing and why precise timing and "freezing things" in the form of a data snapshot is a crucial part of it. What does column A's containing of 1, 2 or 3 represent? What are the five rows about? Are there only five rows? Or are we (i.e., you) dealing with collections of five rows?
And so forth. What is the context in which all of this takes place? What kind of output or report or account are you expecting as a result?
Thank you for the quick response!
The scenario I gave with list 1,2,3 is me testing the formula before using it. In column A I have a status of a project ie "developed", "started", "on hold", "finished" etc. (a total of 8 things on the list). The number of rows is continuously growing as we get new projects and not limited to 5. The goal is to output a date in another column when the status of a project is changed. I don't need previous status changes dates ie if a project is "finished" I don't care what date the cell was changed to "started" I only want the date when the cell was changed to "finished"
The scenario I gave with list 1,2,3 is me testing the formula before using it. In column A I have a status of a project ie "developed", "started", "on hold", "finished" etc. (a total of 8 things on the list). The number of rows is continuously growing as we get new projects and not limited to 5. The goal is to output a date in another column when the status of a project is changed. I don't need previous status changes dates ie if a project is "finished" I don't care what date the cell was changed to "started" I only want the date when the cell was changed to "finished"
If I understand now what you're doing, my suggestion would be that you not use either NOW() or TODAY() as the way to enter dates; rather just use Data Validation to ensure that a date is entered, and use the prompt to specify or describe the correct way to do it. (Presumably something like 10/18/22 or dd/mm/yy)
It's more conventional with databases like this to have the date as the first column in the table, and then the various entries that correspond to what has happened on that day for that project. Without seeing a sample of your actual database, at a minimum I'd recommend something like this as the column headings:
Date ---- Project --- Action/Status --- (Name of person entering) --- Other1 --- Other 2 ---
Excel has wonderful abilities to take a well designed database and extract (on separate "Dashboard" sheets) a current snapshots of (for example)
- status of every ongoing project
- details on any given project
The trick is to make sure that database includes the data on which you'd want to create those summary reports, and then to let Excel "do the heavy lifting" of extracting the meaningful data.
Is it possible to post a copy of your current workbook? If you can't do it here in the forum, put a copy on OneDrive or in GoogleDrive, and post a link here granting access. (Just make sure there's no proprietary or confidential information in it.) Then I or somebody else could offer specific suggestions on how to track these projects most effectively.
