Calculating Vacation Days Based On Time At Company

[trogenone]

Hello
Im new to this and need a hand. Sorry if i have posted this in the wrong area.

I am working on a small database it consists of two tables:

Employees & Holidays

Employees field are:

Employee ID Auto no No duplicates Key
Full Name Text
Start Date Short date field
Length of Service Text/ Date to age query set in the field (Working)

This gives me years served ok. Now in the second table there is another field called Service Entitlement. What i need is for the database to look at the years served and if that employee has served over 2 years he/she get 1 extra day on their holiday allowance. The formula for the extra holiday is:

2 Yrs = 1 extra day
3yrs = 2days
4 = 3days
5 = 4days
6 = 5 days

I hope that makes scence. So in the Holiday table the field marked Service Entitlement should show the number 1 if the employee has served 2 years.

Sorry for the essay but if you can help i would be very greatful.

Kind Regards
Brian Warren

[jaxjagfan]

Hello
Im new to this and need a hand. Sorry if i have posted this in the wrong area.

I am working on a small database it consists of two tables:

Employees & Holidays

Employees field are:

Employee ID Auto no No duplicates Key
Full Name Text
Start Date Short date field
Length of Service Text/ Date to age query set in the field (Working)

This gives me years served ok. Now in the second table there is another field called Service Entitlement. What i need is for the database to look at the years served and if that employee has served over 2 years he/she get 1 extra day on their holiday allowance. The formula for the extra holiday is:

2 Yrs = 1 extra day
3yrs = 2days
4 = 3days
5 = 4days
6 = 5 days

I hope that makes scence. So in the Holiday table the field marked Service Entitlement should show the number 1 if the employee has served 2 years.

Sorry for the essay but if you can help i would be very greatful.

Kind Regards
Brian Warren

Put something similar to this in your query.

Holiday Allowance: iif((DateDiff ("yyyy", [Start Date], Date()) >= 6, 5, iif((DateDiff ("yyyy", [Start Date], Date()) = 5,4,iif((DateDiff ("yyyy", [Start Date], Date()) = 4,3,iif((DateDiff ("yyyy", [Start Date], Date()) = 3,2,iif((DateDiff ("yyyy", [Start Date], Date()) = 2,1,0)))))

Date() is simply today's date. The DateDiff function is returning the number of years ("yyyy") of service between the start date and today's date.

I assumed if less than 2 then 0 days and if great than or equal to 6 then 5 days.

I would have put the data in a table and done a lookup instead of in a formula.

[FishVal]

Nice.

But it has a substantial drawback.
datediff("yyyy", #12/31/1#, #1/1/2#)
will return 1 year. :)
its better to calculate difference in days and divide by 365 (366-days years ignored ;))
int(datediff("d", [StartDate], Date())/365)

Regards,
Fish

[Rabbit]

Nice.

But it has a substantial drawback.
datediff("yyyy", #12/31/1#, #1/1/2#)
will return 1 year. :)
its better to calculate difference in days and divide by 365 (366-days years ignored ;))
int(datediff("d", [StartDate], Date())/365)

Regards,
Fish

To account for the 366-days year you could divide by 365.25 lol.

[missinglinq]

Fish is right on the money! DateDiff() returns a variant (Long Integer) so that, in essence,

DateDiff("yyyy", X, Y)

only returns the difference between the year value for X and Y. The difference in "years" can, indeed, be 1, while the difference in days can be also be 1! Generally speaking, as Fish hinted at, when using DateDiff(), you need to use a time unit lower than the unit you're actually interested in, and then parse the results! So if it's years you're concerned with, you can use days then divide by 365 (or 365.25, to keep Br’er Rabbit happy.) If you were interested in days, which is to say units of 24 hours each, you’d use DateDiff() with hours, then divide by 24.

Welcome to TheScripts!

Linq ;0)>

[FishVal]

To account for the 366-days year you could divide by 365.25 lol.

Let some of those good people receive extra day vacation one or even two days earlier.
lol

[trogenone]

Put something similar to this in your query.

Holiday Allowance: iif((DateDiff ("yyyy", [Start Date], Date()) >= 6, 5, iif((DateDiff ("yyyy", [Start Date], Date()) = 5,4,iif((DateDiff ("yyyy", [Start Date], Date()) = 4,3,iif((DateDiff ("yyyy", [Start Date], Date()) = 3,2,iif((DateDiff ("yyyy", [Start Date], Date()) = 2,1,0)))))

Date() is simply today's date. The DateDiff function is returning the number of years ("yyyy") of service between the start date and today's date.

I assumed if less than 2 then 0 days and if great than or equal to 6 then 5 days.

I would have put the data in a table and done a lookup instead of in a formula.

Thank you i will try this and see how i go. Yes your quite correct if before 2 yrs service 0 extra days and over 6 you still only get the 5.

Thank you again

Brian

[Rabbit]

Please use a clearer title next time.

[trogenone]

Please use a clearer title next time.

DIFFICAULT IF YOUR NOT SURE WHAT YOUR ASKING MR !

[Rabbit]

DIFFICAULT IF YOUR NOT SURE WHAT YOUR ASKING MR !

I can only hope you're joking with this post.