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?

ID	Date	Days Worked
A	04-Jul-15 <tbody> </tbody>	1
A	07-Jul-15 <tbody> </tbody>	1
A	09-Jul-15 <tbody> </tbody>	1
A	10-Jul-15 <tbody> </tbody>	2
A	11-Jul-15 <tbody> </tbody>	3
A	12-Jul-15 <tbody> </tbody>	4
A	13-Jul-15 <tbody> </tbody>	5
A	14-Jul-15 <tbody> </tbody>	6
A	16-Jul-15 <tbody> </tbody>	1

<tbody>
</tbody>

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.