Date Calculations for Staff

We have an extensive Database which handles Staff earned Leave
(Personal & Sick). Everyone gets the same amount of Sick leave each
month- so that is easy. However the amount of persoanl leave earned is
depending on years of service. So I have three calculations each month
to determine leave earned.

The first determines the number of years they have worked with us. so
for December :
DecYr: (#12/1/2004#-[LGD])/365

The second then evaluates the amount of leave earned based on the above
calculation.

VTDec:
IIf([SepYr]=35,21,IIf([SepYr]=30,19,IIf([SepYr]=25,17,IIf([SepYr]=20,15,IIf([SepYr]=15,13,IIf([SepYr]=10,11,IIf([SepYr]=5,10,IIf([SepYr]=2,9,IIf([SepYr]2,8)))))))))

The third kicks in the leave amount when todays date reaches the 1st of
the month.

DecVL: IIf([TDate]=#12/1/2004#,[VTSep],0)

This all works very cleanly, every month the leave is calculated, no
problem..... Except the rule says you do not earn the new leave amount
unless you started work on the 1st. So if you started on the Dec.3rd
of the month, and its your 5 yr of work, you don't go from 9 hrs earned
to 10 hrs until the following month. But my calculations says you are
at 5.00 years as of Dec. 1st. But it should be earned by Jan. Leap
years makes my calculations err.

So if I make a change to the formulas, does anyone have a suggestion.

try

DecYr: IIf(Day([LGD]) 1, (DateDiff("m", [LGD], #12/1/2004#) / 12) - 1,
DateDiff("m", [LGD], #12/1/2004#) / 12)

hth


wrote in message
oups.com...
We have an extensive Database which handles Staff earned Leave
(Personal & Sick). Everyone gets the same amount of Sick leave each
month- so that is easy. However the amount of persoanl leave earned is
depending on years of service. So I have three calculations each month
to determine leave earned.

The first determines the number of years they have worked with us. so
for December :
DecYr: (#12/1/2004#-[LGD])/365

The second then evaluates the amount of leave earned based on the above
calculation.

VTDec:

IIf([SepYr]=35,21,IIf([SepYr]=30,19,IIf([SepYr]=25,17,IIf([SepYr]=20,15,
IIf([SepYr]=15,13,IIf([SepYr]=10,11,IIf([SepYr]=5,10,IIf([SepYr]=2,9,IIf
([SepYr]2,8)))))))))

The third kicks in the leave amount when todays date reaches the 1st of
the month.

DecVL: IIf([TDate]=#12/1/2004#,[VTSep],0)

This all works very cleanly, every month the leave is calculated, no
problem..... Except the rule says you do not earn the new leave amount
unless you started work on the 1st. So if you started on the Dec.3rd
of the month, and its your 5 yr of work, you don't go from 9 hrs earned
to 10 hrs until the following month. But my calculations says you are
at 5.00 years as of Dec. 1st. But it should be earned by Jan. Leap
years makes my calculations err.

So if I make a change to the formulas, does anyone have a suggestion.