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,but 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<11,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.

[NeoPa]

Samuel.

We try to be patient with new posters who struggle to get things quite right :-)

In this case I need you to explain the clarification of your problem more clearly. It is important that it is not a new question as that would require being asked in a separate thread. In this case I think it is simply a clarification.

Unfortunately I don't understand what it is you're trying to explain. Please post it again but more carefully this time. Read it through before submitting to make sure it makes sense.

[SamuelTad]

Ok sir am tying to explain it better.well every employee grants 14 days entitle date for 1st year and continue 15 ... where,year=12.
So far my question regarding available days i mentioned were mistakes.The first entitled date which is 14 days take as a constant to calculate the Available days.But it is clear that employee can take leave so available days automatically decrease so consider my point and support me to finish my project.
Available Days:14+iif(Y<11,Y,11)

[Seth Schrock]

So you would need a field that stores the number of vacation days used. Then, all you need to do is use the formula given and then subtract the value from the new field to get the days left.

[zmbd]

SamuelTad
Question 1) How to calculate the vacation benefit?
I believe that this has been asked and answered at this point.

Question 2) How to keep track of the used vacation and remainder of available benefit?
I also believe that this has been asked and answered at this point. As NeoPa as stated, if you need further assistance with this, please start a new thread. You can refer to this thread for context.

[SamuelTad]

Wow!!! Thank you all who support me a lot to change an ideal one in to real Database.