Calcualting number of days on multiple rows

[Pendelfin]

Hi

I will try to put this as simply as I can for quite a complicated range of
data.

I am trying to prepare some information and flag anyone who has had over 4
calendar weeks absence from work.

Here is my problem.

I have multiple rows of data for each employee showing their absence.

Some absences will be showing as a lump amount ie 34 days. Some will be per
pay period so 28 days then the next 28 days so I need to add these 2
together. Finally some of these are recorded as individual days but it won't
show any data for weekends or bank holidays - therefore someone might have 45
rows of individual days.

Can anyone think of a way I can carry out any calcualtions on this? I have
tried various ideas but short of reviewing everyone individually - which
would probbaly take all year - I can't find a solution.

Many thanks for taking a look at this.

Victoria

 

[JE McGimpsey]

Hi

I will try to put this as simply as I can for quite a complicated range of
data.

I am trying to prepare some information and flag anyone who has had over 4
calendar weeks absence from work.

Here is my problem.

I have multiple rows of data for each employee showing their absence.

Some absences will be showing as a lump amount ie 34 days. Some will be per
pay period so 28 days then the next 28 days so I need to add these 2
together. Finally some of these are recorded as individual days but it won't
show any data for weekends or bank holidays - therefore someone might have 45
rows of individual days.

Can anyone think of a way I can carry out any calcualtions on this? I have
tried various ideas but short of reviewing everyone individually - which
would probbaly take all year - I can't find a solution.

Many thanks for taking a look at this.

One possibility is to use a pivot table, which can summarize by
individual, regardless of how may rows that individual may

However, since I can't see how your data's laid out, I'm not sure what
you mean exactly when you refer to individual days, weekends or bank
holidays - are you saying that the date would be listed instead of a
number? And that weekends and bank holidays shouldn't be counted? If
that's the case, then perhaps using a helper column would help, e.g.:

A B C
1 John Doe 12/24/2008 =NETWORKDAYS(B1,B1,holidays) ==> 1
2 John Doe 12/25/2008 =NETWORKDAYS(B2,B2,holidays) ==> 0

where holidays is a named range with the list of holidays.