A frustration that anyone who has pushed spreadsheets past their intended limits will recognize immediately is captured here. The user has built an Activity progress tracker that works for the current month, but they have hit a wall: how do you capture a live count of in-progress items *and* preserve a snapshot of that same data at month-end, especially when the status itself carries no date? That is not a formula problem. It is a design limitation baked into the spreadsheet model itself.
Traditional spreadsheets treat every cell as a present-tense fact. When you update a status from "In Progress" to "Completed," the old value vanishes. There is no memory of what was true last Tuesday unless you manually copy rows into a separate archive sheet, write a macro, or build a clunky timestamp workaround. The user has already recognized that a start date does not solve the problem, because an in-progress item that started two months ago belongs to every month in between, not just one. They are asking the right question: how do I turn a static snapshot into a living history? The honest answer is that legacy tools were never designed to answer that question gracefully.
What this person needs is not a more clever COUNTIF or an elaborate VBA script. They need a data model that treats time as a first-class dimension, not an afterthought. In an AI-native spreadsheet, every status change would automatically generate a timestamped record. A live view of current in-progress items would be a simple filter on the latest state, and a month-end historical log would be a query that returns the state of every item as of that date, without any manual copying or fragile formulas. The graph they describe would update itself as the data evolves, because the system remembers every transition.
The practical takeaway is this: if you find yourself building workarounds to preserve data that your tool should already remember, you are not the problem. The tool is. The user here has done the hard work of defining what they need, live tracking plus historical snapshots, and that clarity is exactly what should drive a move toward a system that treats data as a timeline, not a snapshot. Stop patching around the limitations. Demand a tool that remembers what you used to know.