uneven ranges and coding

[Lori]

I have a lengthy file of absence data and I am trying to calculate the lenght
of absence (ie number of days) but can't seem to figure this out. I can
easily figure out the 1 day absences but I can't figure out how to do a
formula around the different amount of entries for each person, different
coding for each person, etc.

A B C
EE1 JAN 1/06 SICK1ST
EE1 JAN 2/06 SICKCON
EE1 JAN 3/06 SICKCON
EE1 MAR 1/06 SICK1ST
EE2 FEB 12/06 SICK1ST
EE2 FEB 27/06 SICK1ST
EE3 JUN 12/06 SICK1ST
EE3 JUN 13/06 SICKCON
EE3 JUN 14/06 SICKCON
EE3 JUN 15/06 SICKCON
EE3 JUN 16/06 SICKCON
EE3 JUN 17/06 SICKCON
EE3 SEP 1/06 SICK1ST
EE4 MAR 3/06 SICK1ST
EE4 MAR 4/06 SICKCON
EE4 APR 12/06 SICK1ST
-
Lori16

 

[Eduardo]

Hi Lori,
you can have a summary by type of sickness and then totalize it for example
you prepare a summary by employee and type of sickness

D E F
Employee Sickcon Sick1st
EE1
EE2

Then enter the formula as follow

=SUMPRODUCT(--(A:A=D4),--(c:c=e3))

I Assumed that 1st employee name is in D4 you can adapt the formula to your
needs

 

[Lori]

Sorry... I should have stated that the date of the SICK1ST code is day 1 of
absence but day 2 through end of absence is coded as SICKCON. I need to be
able to pick up the date of the SICK1ST and count up to the end of the
SICKCON and then if there is another SICK1ST for the same EE it adds
separately.

In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a
1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3
would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and
EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence.

I have this to go through for approx 2,000 employees and my datafile is
30,000 lines of data which is why I want to try to automate

 

[Eduardo]

Hi Lori,
here is another option
you need to create a column which will populate just the month so you enter
this formula
+trim(B,3)
The above will bring JAN in the case of JAN 1 / 06. Then create a table as
follow
with the people name in a column and the month of the year lets say in row 2
JAN FEB Etc

Then enter the formula as follow in the first column and copy all the way up
to DEC

=+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2))

What you are doing with the above is counting all the sick days by month
what will give you the results expected

 

[Eduardo]

Hi Lori,
Enter this formula instead of the one sent before in order to avoid having
"0" everywhere

=IF(+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2))=0,"",SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2)))

 

[David Biddulph]

Perhaps you could remove the unnecessary + signs? I guess that you're a
Lotus addict?
= is the Excel way of starting a formula; it doesn't need =+

 

[Eduardo]

Hi David,
Thanks I know that it's not necessary I didn't realize if you see my next
post the + is not there. By the way the last time I used Lotus was in 1986. a
long long time ago