Calculating number of days off

[cathy]

I have a spreadsheet with all employees last names in column "A", their
first names in column "B" and their date of hire in column "C". Rows E - Q
are allocated to "Illnesses 200" listing each month "jan - dec" in a separate
column. The same format is done for "Personal Days", "Vacation Days" etc. I
have 2008 data first followed by 2007 data.

So, when you look at "John Doe" in row 7 - you will see he took one sick day
in January 2008, 3 sick days in March 2008 and 2 sick days in Nov 2007. If
his date of hire is Dec 1,2002 I want to calculate how many sick days he took
as of his anniversary data (Dec 1,2007).

How can I calculate this? Thank you.

 

[Rick Rothstein \(MVP - VB\)]

I assume when you said "Rows E-Q" that you meant "Columns E-Q" although I
would point out that I think your Q should have been P instead (if Jan is in
E and Feb is in F and so on, then Dec would be in P, not Q). And since I
doubt that you put sick day values in the columns before a person is hired,
you should be able to just sum up the 12 columns of values and that will
automatically take care of hire dates taking place sometime within that
year. Assuming you agree with me about the 12 month period starting in E and
ending in P, and unless I misunderstood your question in some way, I think
this should give you what you want...

=SUM(E77)

Rick

 

[Mike H.]

You're going to have to look at the exact days he was sick to get the # days
off up to his anniversary date. Where do you have the raw data that is used
to calculate the information you see on this spreadsheet?

 

[cathy]

Yes it is columns E - Q. Columns E-P represent Jan - Dec. Column Q
represents the sum of all 12 months. The raw data was all on paper. Let me
give an example.

There is an employee whose anniversary is October 28,2002. This employee
took the following vacation days
April 2007 - 5
May 2007 - 1
June 2007 - 6
Dec 2007 - 10
April 2008 - 1
May 2008 - 1

Since his anniversary is in October - I do not want to include any days
prior to his anniversary. So April 2007 - June 2007 days do not count. The
total days I need it to add up are Dec 2007 - 10, April 2008 - 1, May 2008 -
1 for a total of 12 vacation days taken in this anniversary year.

column C - Date of hire
columns E - P - Illness Jan - Dec 2008
column Q - Illness total - 2008
columns T - AE Vacation Jan - Dec 2008
column AF - Vacation Total
columns Ai - AT Personal Jan - Dec 2008
column AU - Personal Total 2008
column AX - BI Leave of Absence Jan - Dec 2008
column BJ - Leave of Absence Total 2008
columns BM - BX Jury Duty - Jan - Dec 2008
column BY - Leave of Absence Total 2008
column CB - CM Illness Jan - Dec 2007
column CN - Illness 2007 Total
column CQ - DB - Vacation Jan - Dec 2007
column DC - Vacation Total 2007
column DF - DQ Personal Jan - Dec 2007
column DR - Personal Total 2007
columns DU - EF Leave of Absence Jan - Dec 2007
column EG - Leave of Absence Total - 2007
column EJ - EU - Jury Dury Jan - Dec 2007
column EV - Jury Dury Total - 2007
column EY - Total Illness days from last anniversary date - I am trying to
program these cells
column EZ - Total Vacation days from last anniversary date - I am trying to
program these cells
column FA - Total Personal days from last anniversary date - I am trying to
program these cells

Hopefully this helps clarify. Looking forward to hearing back. Thanks.

 

[cathy]

Hoping someone can assist. Thanks.
--
Cathy

Yes it is columns E - Q. Columns E-P represent Jan - Dec. Column Q
represents the sum of all 12 months. The raw data was all on paper. Let me
give an example.

There is an employee whose anniversary is October 28,2002. This employee
took the following vacation days
April 2007 - 5
May 2007 - 1
June 2007 - 6
Dec 2007 - 10
April 2008 - 1
May 2008 - 1

Since his anniversary is in October - I do not want to include any days
prior to his anniversary. So April 2007 - June 2007 days do not count. The
total days I need it to add up are Dec 2007 - 10, April 2008 - 1, May 2008 -
1 for a total of 12 vacation days taken in this anniversary year.

column C - Date of hire
columns E - P - Illness Jan - Dec 2008
column Q - Illness total - 2008
columns T - AE Vacation Jan - Dec 2008
column AF - Vacation Total
columns Ai - AT Personal Jan - Dec 2008
column AU - Personal Total 2008
column AX - BI Leave of Absence Jan - Dec 2008
column BJ - Leave of Absence Total 2008
columns BM - BX Jury Duty - Jan - Dec 2008
column BY - Leave of Absence Total 2008
column CB - CM Illness Jan - Dec 2007
column CN - Illness 2007 Total
column CQ - DB - Vacation Jan - Dec 2007
column DC - Vacation Total 2007
column DF - DQ Personal Jan - Dec 2007
column DR - Personal Total 2007
columns DU - EF Leave of Absence Jan - Dec 2007
column EG - Leave of Absence Total - 2007
column EJ - EU - Jury Dury Jan - Dec 2007
column EV - Jury Dury Total - 2007
column EY - Total Illness days from last anniversary date - I am trying to
program these cells
column EZ - Total Vacation days from last anniversary date - I am trying to
program these cells
column FA - Total Personal days from last anniversary date - I am trying to
program these cells

Hopefully this helps clarify. Looking forward to hearing back. Thanks.

 

[Mike H.]

If that is all you get, I don't think you can do it because if a person's
anniversary is on April 15 and you get info that says April 5 days, when were
those 5 days? Before the 15th? After the 15th?

 

[cathy]

ok..... so if I need to add a field, where can I add it to compute the
information I'm looking for?
Is there an alternate way to tackle this situation?

 

[cathy]

never mind. A friend of mine helped me.