Deriving estimated hours worked from an unsorted list of Subversion (SVN) Check-Ins/Submits

[kimberleyhansen]

Hey excel gurus,

Slightly complex one here...and I'm no excel expert. I have a spreadsheet full of SVN check-in times for a group of programmers, as seen below. Note that it is sorted by revision number, not by name, and that it's in reverse chronological order. I want to assign an "time worked" value to each check-in they make. It's just a best-guess value, so it doesn't need to be perfect. This is as far as I've gotten:

• Subtract the time of this check-in from the time of the last check-in made by this person, making sure to take day offsets into account, and subtracting 16 hrs per day (the assumption being that they're only at work 8 hours a day) + weekends and holidays if they did not work (I'd need to make up some kind of table for these)
• In those cases where we do not have a previous check-in, provide an ESTIMATE based on the average amount of time that a check-in takes.
• If a programmer has not checked in for three days, assume they've rolled off the project and 'reset' them...i.e. provide an estimated time the next time they check in, rather than assuming they were working on the project the whole time.

I'm not 100% certain this will be the final algorithm, but it's close. Does this sound doable in Excel? I'm not really even sure where to begin I have a primary key value, so I was thinking I might copy everything to another sheet, sort it by programmer name, do the math, then suck it back into the original sheet...but that seems pretty unstable. Is VBA the way to go here? I'm basically looking for recommendations on a course of action rather than a fully-formed solution, although I'd certainly be grateful for any implementation tips!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Revision[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Time Worked[/TD]
[/TR]
[TR]
[TD]2780[/TD]
[TD]Alan[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-05-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]01:34:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:00:19[/TD]
[/TR]
[TR]
[TD]2781[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Alan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-05-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]01:15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:00:00[/TD]
[/TR]
[TR]
[TD]2782[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Alan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-05-01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]01:15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:00:38[/TD]
[/TR]
[TR]
[TD]2783[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Alan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]23:37:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:08:00 (ESTIMATED based on average of checkins for which we have data)[/TD]
[/TR]
[TR]
[TD]2784[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Barry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]23:33:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:22:14 (23:33 - 17:19 + 24 - 16)[/TD]
[/TR]
[TR]
[TD]2785[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Doris[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]23:29:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:10:36 (23:29 - 20:53 + 24 - 16)[/TD]
[/TR]
[TR]
[TD]2786[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Cam[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]21:26:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:03:09[/TD]
[/TR]
[TR]
[TD]2787[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Cam[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]18:17:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:08:00 (ESTIMATED)[/TD]
[/TR]
[TR]
[TD]2788[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Barry[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]17:19:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:08:00 (ESTIMATED)[/TD]
[/TR]
[TR]
[TD]2789[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl64, width: 65"]Doris[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]2012-04-26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65, align: right"]20:53:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]00:08:00 (ESTIMATED)[/TD]
[/TR]
</tbody>[/TABLE]

Best regards,

K

PS - Please ignore the boxes around some cells in the table...they were automatically added after I submitted, and I don't know how to get rid of them.