434,761 Members | 1,878 Online
Need help? Post your question and get tips & solutions from a community of 434,761 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

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.