| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Database Query Missing | Excel Discussion (Misc queries) | |||
| Count how many weeks have the same value | Excel Worksheet Functions | |||
| count weeks | New Users to Excel | |||
| How do I count the number of even weeks in the current month. | Excel Worksheet Functions | |||
| how to count weeks from date "X" then calculate | Excel Worksheet Functions | |||
Linear Mode
