Counting number of days up to an empty cell then start over again

S

SSG QuarterMaster

What I am trying to do is count the number of days an employee works in a
row. Below is a sample of my data, it is exported from a discoverer query.

****Sample Data****
A B C D E F
Dates name1 name2 name3 name4
1 29-Apr-07
2 30-Apr-07 8.00 8.00 8.00 8.50
3 1-May-07 8.00 8.00 8.00 8.50
4 2-May-07 8.00 8.00 8.00 8.50
5 3-May-07 8.00 8.00 8.50
6 4-May-07 8.00 8.00 8.00 8.50
7 5-May-07 4.00
8 * 7-May-07 8.00 8.00 8.00 8.50
9 8-May-07 8.00 8.00 8.00 8.50

On another worksheet i have the names in column A1:A4. What I want to do is
count the number of days they worked in a row and put that number in cell
B1:B4 then to start back up after an empty cell and place the next total in
the next cell to the right.

****Example of desired output****

A B C D
name1* 6 2
name2 3 1 2
name3 5 2
name4 5 2

*= There was a break in dates because nobody worked that day. The data will
span approximately a years worth, some days wil appear for some and not for
others based on if they worked on a saturday or sunday or both. The end goal
is to monitor the number of days in a row that an employee works without a
day off. I will then do conditional formating to shade those that are over 12
days in a row.

Not sure if this is possible in a formula or if it will require VB coding.
Any help in resolving this would be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top