434,962 Members | 2,545 Online
Need help? Post your question and get tips & solutions from a community of 434,962 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
9 Replies

 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

 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

 Expert Mod 10K+ P: 12,366 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

 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

 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

 Expert Mod 10K+ P: 12,366 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

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