| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating number of days off
Hoping someone can assist. Thanks.
-- Cathy "cathy" wrote: 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 "Mike H." wrote: 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" wrote: 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. -- Cathy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculating the number of days continuation | Excel Discussion (Misc queries) | |||
| Calculating the number of minutes between days | Excel Worksheet Functions | |||
| calculating number of days between first day to last day, inclusiv | Excel Worksheet Functions | |||
| Calculating number of days | Excel Worksheet Functions | |||
| Calculating number of days in month | Excel Programming | |||
Linear Mode
