count number of weeks missing for each individual in a database
 

Hi,

I need to measure attendace for the employees. I have a list of weeks worked
(eg. below) for each employee (numbered from 1-54) and I need to work out the
weeks that have not been worked and whether they are consecutive or not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17

count number of weeks missing for each individual in a database
 

Hi,

If Bob's weeks worked are in range B2:B7, then the weeks missing would be

=52-count(B2:B7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pumpkin" wrote in message
...
Hi,

I need to measure attendace for the employees. I have a list of weeks
worked
(eg. below) for each employee (numbered from 1-54) and I need to work out
the
weeks that have not been worked and whether they are consecutive or not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17

count number of weeks missing for each individual in a database
 

The number of missing weeks is =54-COUNT(A2:A55)
I will let someone else answer the non-consecutive part. The easiest way
would be with a helper column, but probably someone can think of a more
direct way.
--
David Biddulph

"pumpkin" wrote in message
...
Hi,

I need to measure attendace for the employees. I have a list of weeks
worked
(eg. below) for each employee (numbered from 1-54) and I need to work out
the
weeks that have not been worked and whether they are consecutive or not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17

count number of weeks missing for each individual in a databas
 

Thanks ashish, You seem to be a whizz at this :-)
I need to count the consecutive weeks together...i.e. Bob was off work in
weeks 1 and 2 and also 5,6 and 7 so that woud be 2 weeks and 3 weeks
consecutively taken off...do you know of a way to do this?

pumpkin

"Ashish Mathur" wrote:

Hi,

If Bob's weeks worked are in range B2:B7, then the weeks missing would be

=52-count(B2:B7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pumpkin" wrote in message
...
Hi,

I need to measure attendace for the employees. I have a list of weeks
worked
(eg. below) for each employee (numbered from 1-54) and I need to work out
the
weeks that have not been worked and whether they are consecutive or not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17

count number of weeks missing for each individual in a databas
 

Hi,

Not celar about your question. Please explain the following:

1. The answer for Bob;
2. Logic for 1 above

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pumpkin" wrote in message
...
Thanks ashish, You seem to be a whizz at this :-)
I need to count the consecutive weeks together...i.e. Bob was off work in
weeks 1 and 2 and also 5,6 and 7 so that woud be 2 weeks and 3 weeks
consecutively taken off...do you know of a way to do this?

pumpkin

"Ashish Mathur" wrote:

Hi,

If Bob's weeks worked are in range B2:B7, then the weeks missing would be

=52-count(B2:B7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pumpkin" wrote in message
...
Hi,

I need to measure attendace for the employees. I have a list of weeks
worked
(eg. below) for each employee (numbered from 1-54) and I need to work
out
the
weeks that have not been worked and whether they are consecutive or
not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17

count number of weeks missing for each individual in a databas
 

Make sure that you've got a row with zeroes (or blanks) at the beginning of
the list, before week 1, then use =IF(A3-A2<1,A3-1-A2,"") and copy down, to
show the number of weeks absent in each absence period.
--
David Biddulph

"pumpkin" wrote in message
...
Thanks ashish, You seem to be a whizz at this :-)
I need to count the consecutive weeks together...i.e. Bob was off work in
weeks 1 and 2 and also 5,6 and 7 so that woud be 2 weeks and 3 weeks
consecutively taken off...do you know of a way to do this?

pumpkin

"Ashish Mathur" wrote:

Hi,

If Bob's weeks worked are in range B2:B7, then the weeks missing would be

=52-count(B2:B7)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pumpkin" wrote in message
...
Hi,

I need to measure attendace for the employees. I have a list of weeks
worked
(eg. below) for each employee (numbered from 1-54) and I need to work
out
the
weeks that have not been worked and whether they are consecutive or
not.

Please help :-)

bob jack
3 2
4 13
8 14
9 15
10 16
11 17