Trouble measuring progress/change of an activity over time for specific people

Hallo dear gals & gents,

Ok, so I’m on the verge of wrapping up a dashboard that I’m under tons of pressure to finish, but am utterly lost on one question – how to measure time in Excel, especially with regards to progress?

I want the workbook I’m creating to be shared in real time over Google Docs, right? And I have cells showing a variety of information – status of an activity, who is in charge of the activity, the related numbers, etc.

I basically have to show 2 things:

1. The movement from one status to another over time.

It's more complicated than this, but say I have an "Activity" column, a "Segment" column - the activities are segmented into sub-types, a "User" column, and a "Status" column. For instance, Jeremy has converted the status of 20 activities for this sub-segment from “Resting” status to “Followed Up” status this week, and the status of 5 activities from “Followed Up” to “Resolved” in the last month. The ideal to see how much progress a person has made over a period of time for a lot of different activities,in such a way that you can summarize in the dashboard something like: "Number of activities Jeremy has converted from this segment into the 'Resolved' status within the last week".

Likewise, say, being able to measure something like, “how many activities were added to Owen’s plate this week?” (if Owen is listed in the pull down of the “User” column, related to the “Activity” column).

Is this doable? I know Google Docs saves old versions of the worksheet, but figured it's a pipe dream to have it access what was in a particular cell a week ago for you to compare with today's status/activities.

2. I also need to show the cycle time, meaning, how long it takes someone to get an activity from the initial status all the way to the end (and perhaps even with some intermediate time measurements, e.g., from the initial to the midpoint status, then from the midpoint til the end).

My idea was to have a cell next to the status, for the person to fill in the date whenever the status changed from one to another. My boss got pissed and hated the idea (though he knows I’m hardly an Excel guru) because he doesn’t want any additional manual entry, which I totally understand. So I’m wondering if there’s a way to have a cell auto-populate with that day’s date the moment another cell is filled (in such a way that I can use other cells to count the time elapsed from that date, matched up against other completion dates). Maybe there’s a better way to do this, not sure.

Any pointers would be taken with everlasting gratitude. Let me know if you need me to clarify anything or provide further detail. Thanks so very much for your guidance & insight!

My best,
R.