Annual leave format

[SamuelTad]

Hi All,
Am trying some Annual leave database.
It's Idea is

Annual leave:[Date of Employ]+1,for 1st year=14 days
for 2nd year=14+(14+1)
.
.
.
I need the expression which execute Annual leave in my case. with regard!

[Seth Schrock]

I think that I understand your policy to be Years of Employment * 14 + 1. I'm guessing that there is probably a cutoff, otherwise once you have worked 13 years, you would only have to work half the year. So we need a more detailed explanation of what the policy is.

Also are you looking for a VBA or SQL solution?

[SamuelTad]

Thanks for your concern!@Seth
No matter how many year employee can work in the company.However ,employee has to consume entitled no of days within the year before new Entitle days released.The first year entitled annual leave shall be used on that first year only unless some exceptional case.I need SQL based.
Entitled Days for 1st,begin with 14 days
2nd year,14 days + 15 Days = 29 Days
it goes like this.

[zmbd]

Can you show your work?

What I would do is a variation on Allen Browne's Age() Function

Using this function in the query, feed it the employ's hire date. Now you have the "Age" of the employee with the company.
(personally, I'd do this in one field)

You can then use iif(age=>1,14+( 14+(age-1)),14)
(personally, I'd do this in a second field... however, could do this all in one field)

As written, this will default to 14 days from age 0 thru age 1.999999(...), You will need additional logic if you don't want to award before the 1 year anniversary and if you need a cutoff such as only 60 days, etc... you can do this with nested IIF() or, my preference would be, Switch Funciton (,as I find nested iif() to be difficult to maintain).

[Rabbit]

So... continuing the trend... the third year they get 14+15+16 = 45 days of leave in a year??? I want to work at your company. After enough years, I won't have to work at all.

[NeoPa]

Samuel. Like Rabbit, I think you have misunderstood what you've been asked to do.

While it is not unusual for an employee to be awarded an extra day's leave per year of employment up to some maximum, it is extremely unusual, not to say obviously daft, to get an extra fortnight for every year worked. It is also unheard of for there to be no maximum set.

Please check what it is you have been asked to do and come back here and post two things for us :

1. The corrected situation explained clearly and logically.
2. The position (title) of the person it was who asked you to do this for them.

With this information we can proceed in a sensible manner.

[SamuelTad]

Thank you All.I was stressed only on code rather than fulfilling all requirements,bu t i understood that maximum days entitled is a part of a code so it is 25 days.
@ NeoPa my inquiry is
1.Code which Implement annual leave.
2.You can take the same Entitled Days for employee.
***One thing to remind if i get any clue i will manage in for different position.Thanks !!

[Seth Schrock]

I'm confused. In your first post you say that the second year you get 14+(14+1) which equals 29 so how is the maximum days only 25?

[SamuelTad]

Sorry inconvenience that i made @ Seth!would mind able to understand me in such manner.
1st year grant 14 days
2nd year grant 15 days
.
.
.
12nd year granted 25 days.So it will stop at this time.So it is not the total number of days granted rather i want mentioned that granted date increment with 1 day for every consecutive years.Thank you!!

[Seth Schrock]

Okay. Starting to make sense. So the formula would be 14 + (y - 1) where y is the number of years worked.

Year   Formula        Days off
1      14 + (1 - 1)   14
2      14 + (2 - 1)   15
3      14 + (3 - 1)   16
...
11     14 + (11 - 1)  24
12     14 + (12 - 1)  25

Is this correct?

[SamuelTad]

Thanks Seth your formula is workable.In my database there is Date of employ column in DD.MM.YYYY format.How can number of years worked count by taking Date of Employ .With regard!!!!
Finally my intention is the system to show me this,
Available Days:14+(Nz[Date of employ]-1)

[NeoPa]

Right. Now we have a question to work with that makes some sense :-)

@Seth.
Your formula doesn't appear to be an accurate reflection of the requirement. If you consider that y is the number of whole years worked then you would need :

14 + IIf(y < 11, y, 11)

@Samuel.
You still haven't given us the position of the person it was who requested this from you. Please do so ASAP. Failure to do so may result in the deletion of this thread.

Although this question now makes sense it is important to understand that this formula results in a different value depending on the date that is used to determine y. If you're happy that Date() (IE. The current date.) is used then this is straightforward enough. If not, then you need to get a date from somewhere. Probably a form, but certainly specified somewhere.

Unfortunately, even here we have a problem. Calculating an accurate value of whole years is not as easy as it should be. Checking it is, but calculating it isn't. DateDiff() returns a value of the nearest number of years. This is not a great deal of use and not appropriate for your needs :-(

The logic for calculating y (and then [Available Days]) in this scenario would be :

y: Year(Date())-Year([Date of Employ])-IIf(Format(Date(),'mmdd')<Format([Date of Employ]),'mmdd'),1,0)
Available Days: 14 + IIf(y < 11, y, 11)

As I say, not too straightforward .

[SamuelTad]

Thanks @Neopa,OK to draw the company i work for is a Herbal farm,It's man power is very few management stuff and 400 ordinary staff.even if they differ in position but Similar Entitled Date .So our intention is to manege the 400 people annual leave system.

[NeoPa]

OK. Although that doesn't actually answer the question it does give me enough information to know this is not an educational assignment. Good enough.

Now we need to know if this has solved your problem or if you are still looking for further help with this issue?

[SamuelTad]

Thank you @NeoPa!!
one more question!
Available Days:14+IIf(Y<1 1,Y,11) on this do not take 14 is a constant rather first year entitle date.So it can be zero when employee use it.Remember am an Amateur on programming so i hope you tolerate me.