L

#### Llobid

secretaries go through for each employee of the school district. Eac

employee receives a set amount of "New State" personal leave each year

They also receive varying amounts of "Local" leave each year...dependin

on the length of their contracts. The secretaries of each campus an

department keep a ledger showing the dates of leave taken for eac

employee in their campuses or departments.

The problem we have encountered is as follows:

There are actually 3 categories of leave...each allowing only specifi

types of absences (reasons for the absence). The absences must b

deducted from the categories in the following order and only for th

allowed types.

_First:_ Local Leave - Types 1, 2, 3, 4, and 8

_Second:_ New State Leave - Types 1, 2, 3 , 4, 5, 6, 7, and 8

_Last:_ Old State Sick Leave - Types 1, 2, 3, and 7

The leave types are Personal Illness, Bereavement, Personal Business

etc.

The spreadsheet is laid out as follows (PDF file attached if needed):

*Column A:* Date of Absence

*Columns B - I:* 8 absence types

*Column J:* "New Added Local Leave"...must be considered in the formul

as an addition.

*Column K:* "Local Leave"...row 10 holds the employee's beginnin

balance of leave...row 11 holds the formula.

*Column L:* "New Added State Leave"...must be considered in the formul

when adding leave to "Personal New State Leave".

*Column M:* "Personal New State Leave"...row 10 holds the employee'

beginning balance of leave...row 11 holds the formula.

*Column N:* "Old State Sick Leave"...grandfathered in for us old folks

so no new leave is ever added...when it's gone, it's gone. Row 10 hold

the employee's beginning balance...row 11 holds the formula.

I have figured the formulas for figuring Local and New Stat

successfully, since the New State comes after the Local and include

all the leave types of Local...plus some. The problem comes when tryin

to figure the formula for the Old State Sick Leave. Since it can only b

used for a portion of the types as the New State, the formula must tak

into consideration what has already been deducted in Local and Ne

State...without duplicating the deductions.

It would be much easier if the secretaries only put one absence pe

row...but that is not always the case. Sometimes they may put a dat

range...with multiple entries under more than one absence type, so th

formulas have to be foolproof, so that the data is accurate. If not

some employees may be left with less leave than they are entitled to.

I have it figured so that the allowed leave types are taken from Loca

when applicable...and only until the balance <= 0...then th

spreadsheet shows "0" and takes the remainder from New State...and s

on.

I have been pulling what hair I have left out. Just when I think I hav

a formula that seems to work, I discover a circumstance in which i

doesn't. To give you an example of the kinds of formulas I've bee

working with, below are the formulas I currently have for "Local" an

"New State" leave.

=IF(ISBLANK(A11),"",IF(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)<0),0,IF(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)>=0),K10+J11-SUM(B11:E11,I11),K10+J11)))

=IF(ISBLANK(A11),"",IF(OR(M10=0,M10=""),0,IF(AND(K10+J11-SUM(B11:E11,I11)>=0,M10+L11-SUM(F11:H11)>=0),M10+L11-SUM(F11:H11),IF(AND(K10+J11-SUM(B11:E11,I11)>=0,M10+L11-SUM(F11:H11)<0),0,IF(OR(AND(OR(B11>0,C11>0,D11>0,E11>0,I11>0),K10+J11-SUM(B11:E11,I11)<0,M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11)>0),AND(OR(F11>0,G11>0,H11>0),M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11)>=0)),M10+L11+(K10+J11-SUM(B11:E11,I11))-SUM(F11:H11),0)))))

I just can't seem to come up with a foolproof formula for Old Stat

Sick Leave that would not sometimes deduct from the wrong type o

duplicate deductions. If there is no leave left in either Local or Ne

State, any non-allowable absences from Old State should do nothing.

I would certainly appreciate any help you could give me. I would pos

the original spreadsheet...but I see the xls attachment is not allowed.

+-------------------------------------------------------------------+

|Filename: LeaveForm.PDF |

|Download: http://www.excelforum.com/attachment.php?postid=2765 |

+-------------------------------------------------------------------+