Excel 2007 counting across rows to the next blank cell

[KJ2607]

Hi There,

I'm looking at writing a formula to count the number of days worked since an employees last day off (results to appear in E7)

From how my spreadsheet is designed, I'm needing to count the number of start/finish cells in Row 7 (F7:G7,J7:K7, and so on) until I get to a blank start/finish cell (and then I'm assuming I'll need to divide by 2 at the end of the formula to get my total number of days) I then need to start counting again once the spreadsheet is update till the next blank cell.

Is this possible? I'm only recently delving into the more complicated formula so any advice/suggestions would be appreciated

[TABLE="width: 1162"]
<colgroup><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Andrew Poulsom]

Can we see some sample data please and the expected result?

 

[KJ2607]

Hi Andrew,

I can seem to attach my actual spread sheet so I've create a mock of what I'm hoping to try:

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"] [/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[TD="class: xl66, width: 64"]G[/TD]
[TD="class: xl66, width: 64"]H[/TD]
[TD="class: xl66, width: 64"]I[/TD]
[TD="class: xl66, width: 64"]J[/TD]
[TD="class: xl66, width: 64"]K[/TD]
[/TR]
[TR]
[TD="class: xl66"]1[/TD]
[TD="class: xl68"]Name[/TD]
[TD="class: xl69, width: 64"]Total Days Since Last Day Off[/TD]
[TD="class: xl70, width: 64"]Shift 1 Start[/TD]
[TD="class: xl70, width: 64"]Shift 1 Finish[/TD]
[TD="class: xl70, width: 64"]Shift 1 Start[/TD]
[TD="class: xl70, width: 64"]Shift 2 Finish[/TD]
[TD="class: xl70, width: 64"]Shift 3 Start[/TD]
[TD="class: xl70, width: 64"]Shift 3 Finish[/TD]
[TD="class: xl70, width: 64"]Shift 4 Start[/TD]
[TD="class: xl70, width: 64"]Shift 4 Finish[/TD]
[TD="class: xl70, width: 64"]Shift 5 Start[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]J Smith[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]08:00[/TD]
[TD="class: xl67"]17:00[/TD]
[TD="class: xl67"]08:00[/TD]
[TD="class: xl67"]17:00[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"]08:00[/TD]
[TD="class: xl67"]17:00[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[/TR]
</tbody>[/TABLE]

So what I want to work out is how many days have passed since the employee had a day off, so J Smith in A2 worked 2 days before having a day off (G2:H2 are blank), but then worked another day before having a day off in K2.

I'm hoping for the answer to be entered into B2 every time the spreadsheet is updated - does this make sense?

 

[Andrew Poulsom]

So what result do you expect in B2 with that data? How far does your data extend columnwise?

 

[KJ2607]

So for now the result in B2 would be "1" as the employee worked Shift 4 (I2:I3 My actual spreadsheet goes on for a number of columns as it contains several weeks worth of Employee shifts - If I can understand how to write a formula for the above mock spreadsheet I should be able to adapt it for my actual spreadsheet containing the real data

Is there any way I can upload my actual spreadsheet as that may give a better understanding?

 

[Andrew Poulsom]

With you sample data the last day off is Shift 5 not Shift 3. If your data ends in column J:

=COUNTIF(INDEX(C2:J2,MAX(INDEX((C2:J2="")*(COLUMN(C2:J2)-COLUMN(C2)+1),))+1):J2,">0")/2

returns 1.

 

[KJ2607]

That's Great Thanks Andrew!

I think I definitely need to read up more on this type of formula!

Thanks once again