By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Calculating Vacation Days Based On Time At Company

P: 16
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
Dec 20 '07 #1
Share this Question
Share on Google+
9 Replies


jaxjagfan
Expert 100+
P: 254
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.
Dec 20 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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
Dec 20 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
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.
Dec 20 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
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)>
Dec 20 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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
Dec 20 '07 #6

P: 16
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
Dec 21 '07 #7

Rabbit
Expert Mod 10K+
P: 12,315
Please use a clearer title next time.
Dec 21 '07 #8

P: 16
Please use a clearer title next time.

DIFFICAULT IF YOUR NOT SURE WHAT YOUR ASKING MR !
Dec 26 '07 #9

Rabbit
Expert Mod 10K+
P: 12,315
DIFFICAULT IF YOUR NOT SURE WHAT YOUR ASKING MR !
I can only hope you're joking with this post.
Dec 28 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.