434,761 Members | 1,878 Online + Ask a Question
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

✓ 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.

3 Replies

 100+ P: 214 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 Dim dblPayDays As Double Dim intPartMonth As Integer Dim intMonths As Integer   'Determine how many whole months have elapsed from 1 Jan of current year intMonths = DateDiff("m", DateSerial(Year(Date), 1, 1), Date)   'Determine how many pay days there have been in current month If Date = DateSerial(Year(Date), Month(Date) + 1, 0) Then     intPartMonth = 2   ElseIf DatePart("d", Date) >= 15 Then     intPartMonth = 1   Else     intPartMonth = 0 End If   dblPayDays = (Me.ctlDaysPerYear / 24) * ((intMonths * 2) + intPartMonth)   Me.ctlCurrentAccrued = dblPayDays   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

 100+ P: 214 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 