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

Code/Expression to Calculate Accrued Time Off

P: 6
Moving Excel HR data into an Access database to better manage the information and I've come across a problem trying to figure out how to write the code/expression to calculate accrued time off. I have a field that contains each employee's allowed time (in hours) since each person has their own deal regardless of years of service. There are 24 pay periods (15th and last day of the month) in the year and the employee gets credit for 1/24th of their allowed time with each pay period (e.g. someone who is allowed 120 hours a year earns 5 hours each pay period). So I want this expression to calculate the accrued time based upon the current date as of the 1/1 start date (it resets each January 1st). The Excel formula looks like this (I tried adapting it to Access):

ROUNDDOWN(IF([Employment Status]="Active",(MONTH(Now())-1)*((([Vacation Hours Allowed]/24)*2))+((DAY(Now())>=15)+(DAY(Now()+1)=1))*(([Vacation Hours Allowed]/24))
-IF(YEAR([Hire Date])=YEAR(Now()),(MONTH([Hire Date])-1)*((([Vacation Hours Allowed]/24)*2))+((DAY([Hire Date])>=15)+(DAY([Hire Date]+1)=1))*(([Vacation Hours Allowed]/24)),0),0),2)

Hopefully, I've given you enough info to go on. Any help would be greatly appreciated. Thanks.
Feb 17 '17 #1

✓ answered by gnawoncents

Just to make sure I'm clear, you want the total number of accrued hours based on the current date, with 1/24th of the yearly total accruing each pay period (15th and last day of month). Is this accurate?

Regardless, modifying the code below to your needs should get you where you want to go.

Expand|Select|Wrap|Line Numbers
  1. Dim dblPayDays As Double
  2. Dim intPartMonth As Integer
  3. Dim intMonths As Integer
  4.  
  5. 'Determine how many whole months have elapsed from 1 Jan of current year
  6. intMonths = DateDiff("m", DateSerial(Year(Date), 1, 1), Date)
  7.  
  8. 'Determine how many pay days there have been in current month
  9. If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
  10.     intPartMonth = 2
  11.   ElseIf DatePart("d", Date) >= 15 Then
  12.     intPartMonth = 1
  13.   Else
  14.     intPartMonth = 0
  15. End If
  16.  
  17. dblPayDays = (Me.ctlDaysPerYear / 24) * ((intMonths * 2) + intPartMonth)
  18.  
  19. Me.ctlCurrentAccrued = dblPayDays
  20.  

Share this Question
Share on Google+
3 Replies


gnawoncents
100+
P: 212
Just to make sure I'm clear, you want the total number of accrued hours based on the current date, with 1/24th of the yearly total accruing each pay period (15th and last day of month). Is this accurate?

Regardless, modifying the code below to your needs should get you where you want to go.

Expand|Select|Wrap|Line Numbers
  1. Dim dblPayDays As Double
  2. Dim intPartMonth As Integer
  3. Dim intMonths As Integer
  4.  
  5. 'Determine how many whole months have elapsed from 1 Jan of current year
  6. intMonths = DateDiff("m", DateSerial(Year(Date), 1, 1), Date)
  7.  
  8. 'Determine how many pay days there have been in current month
  9. If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then
  10.     intPartMonth = 2
  11.   ElseIf DatePart("d", Date) >= 15 Then
  12.     intPartMonth = 1
  13.   Else
  14.     intPartMonth = 0
  15. End If
  16.  
  17. dblPayDays = (Me.ctlDaysPerYear / 24) * ((intMonths * 2) + intPartMonth)
  18.  
  19. Me.ctlCurrentAccrued = dblPayDays
  20.  
Feb 17 '17 #2

P: 6
Thanks for the code, but it seems to have an issue with "Me". Any idea why?

To answer your question, yes, I want it to calculate the hours accrued since 1/1/17 to the most current pay period. So for instance, at this point, February 20th, using my above example, there have been 3 pay periods in 2017, so the employee will have accrued 15 hours to this point.
Feb 20 '17 #3

gnawoncents
100+
P: 212
The "Me." reference will only work if that control is one on your current form. You will need to replace Me.ctlDaysPerYear with a reference to your field that holds the days per year allotted to the employee, and Me.ctlCurrentAccrued with the field where you want to store the number of days accrued to-date.
Feb 21 '17 #4

Post your reply

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