Date calculations and sum
Good afternoon
I am building a spreadsheet to follow up on vacation taken by employees.
One of the constraints I have is to calculate for comp days given by
managers to their employees. Comp days:
- have to be taken before a certain date,
- may be given one to many times to an employee
So my rows would look like:
Employee Comp Days on or before
A 1 5/5/06
A 2 9/6/06
B 2 11/1/06
C 1 9/10/06
In another part of the sheet I have the number of vacation days an employees
can take by period of time. So my rows would look like
Employee VacDays From To
A 10 4/5/06 5/10/06
A 15 5/11/06 10/1/06
B 10 10/1/06 12/31/06
C 10 8/10/06 12/12/06
In this situation the number of vacation days for employee A is
10 Vac days+1 comp days from 4/5/06 to 5/10 06 (because of the comp days
matching the period 4/5/06 to 5/10/06)
15 Vac days+2 comp days from 4/5/06 to 5/10 06 (because of the comp days
matching the period 5/11/06 to 10/1/06).
How do you test date interval and if in a date interval add it to another
cell in a spreadsheet...
Any help or direction is welcome
|