Counting Consecutive Days

[Prognosis]

Good afternoon everyone,

I have a set of data that has ID and Date. I need to create a new column with Days Worked that is a formula to solve how many consecutive days someone works. If you want to go 1 step above that, we need to be sure ID's match. Data will be sorted by ID then by Date (oldest to new).

Any ideas?

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Days Worked[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]04-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]07-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]09-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]10-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]11-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]12-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]13-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]14-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][TABLE="width: 88"]
<tbody>[TR]
[TD="class: xl66, width: 88"]16-Jul-15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

*edit - wow that table does not look like i expect. Sorry about that.

 

[Prognosis]

I've come up with something along these lines: (dates are in column B)

=1+IF(B6=B5+1,1,0)+IF(B6=B4+2,1,0)

but there as to be a better way! I dont know how many consecutive days there could be so i wouldnt want to write this out for 8 days and someone work a 9th day and it not be recognized in the equation.

Thoughts?

 

[dreid1011]

Perhaps this:

=IF(B2-B1=1,C1+1,1)

This is intended for data starting in row 2, as row 1 will be blank or headers. Can't start it in row 1, because there is no row 0 to reference above it. Copy down as far as you need.