[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • Last Modified:

I need VBA Code to to figure payroll hours and pay amount_now to the nearest 15 minutes

Original Question: I have a Work Hours Table and I want to enter A [StartTime]  and and an [EndTime] and it will go over 24hours.  I need code to figures hours elapsed to the nearest half hour, and I need to be able to preform an expression to figure the pay, like [Hours]*[PayRate] to yeaild me total pay.  I would like this to be Public Function and useable in multiple Reports.  I have found a code to yeild me the Total Hours but it formats it in Hours:Minutes and I can't figure how to multiply it by my [PayRate].  I have attached the code I found.
Using, Access 2007

Now It seems I need it to be to the nearest 15 min,  I've attached the code that worked to the nearest 30 minutes.
Public Function ElapsedHours(StartTime, EndTime)
    'figures the number of hours elapsed between the two dates
    
    Dim mMins As Integer
    Dim mHours As Double
    Dim mRemainder As Double
    
    mMins = DateDiff("n", StartTime, EndTime)
 
    mHours = Int(mMins / 60)
    mRemainder = (mMins) - (mHours * 60)
    If mRemainder >= 45 Then
        mHours = mHours + 1
    ElseIf mRemainder >= 30 Then
        mHours = mHours + 0.5
    ElseIf mRemainder >= 15 Then
        mHours = mHours + 0.5
    End If
    
    ElapsedHours = mHours
    
End Function

Open in new window

0
bdgbrick
Asked:
bdgbrick
1 Solution
 
RyanProject Engineer, ElectricalCommented:
define your parameters in your function explicitly.

StartTime as date, EndTime as Date.

In your query, the time should be a date/time format, and it'll workout pretty easily. Theres a problem if the dates cross over midnight unless you have a date included with your time.

If all you're wanting is this code modded to 15min then...
Public Function ElapsedHours(StartTime, EndTime)
    'figures the number of hours elapsed between the two dates
    const FACTOR = 15
    Dim mMins As Integer
    
    mMins = DateDiff("n", StartTime, EndTime)
    
    ElapsedHours = round(mMins/FACTOR,0) * FACTOR /60
 
End Function

Open in new window

0
 
bdgbrickAuthor Commented:
Great Thanks so much.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now