mployeeID

EmployeeMonHrs

EmployeeTueHrs

EmployeeWedHrs

EmployeeThuHrs

EmployeeFriHrs

EmployeeSatHrs

EmployeeSunHrs

EmployeeBasicPay

What I want to do is divide EmployeeBasicPay with the Total number of hours.

All Hrs fields are formatted as Date/Time (Short Time)

The code below is what I managed upto now. I know there's something wrong with the method used to calculate time but what?

CODE

Function GrossWage(BasicPay As Double, PayFreq As String, EngDate As Date, TermDate As Date, DateFrom As Date, DateTo As Date, MonHrs As Date, TueHrs As Date, WedHrs As Date, ThuHrs As Date, FriHrs As Date, SatHrs As Date, SunHrs As Date) As Double

Dim j As Integer

Dim TotHrsWorked

TotHrsWorked = 0

Flag = ""

If EngDate > DateFrom Then DateFrom = EngDate: Flag = "T"

If TermDate <> 0 Then

If TermDate < DateTo Then DateTo = TermDate: Flag = "T"

End If

If Flag = "T" Then

For j = 1 To 7

DysWorked = WeekdaysCount(DateFrom, DateTo, Right(Str(j), 1)) '<----Can I improve this statement to send a one string character.

If j = 1 Then TotHrsWorked = DysWorked * SunHrs

If j = 2 Then TotHrsWorked = TotHrsWorked + (DysWorked * MonHrs)

If j = 3 Then TotHrsWorked = TotHrsWorked + (DysWorked * TueHrs)

If j = 4 Then TotHrsWorked = TotHrsWorked + (DysWorked * WedHrs)

If j = 5 Then TotHrsWorked = TotHrsWorked + (DysWorked * ThuHrs)

If j = 6 Then TotHrsWorked = TotHrsWorked + (DysWorked * FriHrs)

If j = 7 Then TotHrsWorked = TotHrsWorked + (DysWorked * SatHrs)

Next

GrossWage = (BasicPay / (MonHrs + TueHrs + WedHrs + ThuHrs + FriHrs + SatHrs + SunHrs)) * TotHrsWorked

Exit Function

End If

If PayFreq = "M" Then

GrossWage = (BasicPay * 52 / 12)

ElseIf PayFreq = "F" Then

GrossWage = (BasicPay * 2)

ElseIf PayFreq = "4" Then

GrossWage = (BasicPay * 4)

ElseIf PayFreq = "W" Then

GrossWage = (BasicPay * 1)

Else

MsgBox("Error in Payment Frequency ID Codes. Only M, W, F and 4 are supported.", ,"Incorrect Variables Passed On")

Exit Function

End If

End Function

Function WeekdaysCount(DateFrom As Date, DateTo As Date, WD As String) As Integer

'Counts Weekdays specified by 'WD' where 1 = Sunday, 2 = Monday, etc

Dim Counter As Integer

Dim i As Date

Dim d As Integer

Dim f As Integer

Counter = 0

For f = 1 To Len(WD)

d = Val(Mid(WD, f, 1))

For i = DateFrom To DateTo

If Weekday(i, vbSunday) = d Then Counter = Counter + 1

Next i

Next f

WeekdaysCount = Counter

End Function

Dim j As Integer

Dim TotHrsWorked

TotHrsWorked = 0

Flag = ""

If EngDate > DateFrom Then DateFrom = EngDate: Flag = "T"

If TermDate <> 0 Then

If TermDate < DateTo Then DateTo = TermDate: Flag = "T"

End If

If Flag = "T" Then

For j = 1 To 7

DysWorked = WeekdaysCount(DateFrom, DateTo, Right(Str(j), 1)) '<----Can I improve this statement to send a one string character.

If j = 1 Then TotHrsWorked = DysWorked * SunHrs

If j = 2 Then TotHrsWorked = TotHrsWorked + (DysWorked * MonHrs)

If j = 3 Then TotHrsWorked = TotHrsWorked + (DysWorked * TueHrs)

If j = 4 Then TotHrsWorked = TotHrsWorked + (DysWorked * WedHrs)

If j = 5 Then TotHrsWorked = TotHrsWorked + (DysWorked * ThuHrs)

If j = 6 Then TotHrsWorked = TotHrsWorked + (DysWorked * FriHrs)

If j = 7 Then TotHrsWorked = TotHrsWorked + (DysWorked * SatHrs)

Next

GrossWage = (BasicPay / (MonHrs + TueHrs + WedHrs + ThuHrs + FriHrs + SatHrs + SunHrs)) * TotHrsWorked

Exit Function

End If

If PayFreq = "M" Then

GrossWage = (BasicPay * 52 / 12)

ElseIf PayFreq = "F" Then

GrossWage = (BasicPay * 2)

ElseIf PayFreq = "4" Then

GrossWage = (BasicPay * 4)

ElseIf PayFreq = "W" Then

GrossWage = (BasicPay * 1)

Else

MsgBox("Error in Payment Frequency ID Codes. Only M, W, F and 4 are supported.", ,"Incorrect Variables Passed On")

Exit Function

End If

End Function

Function WeekdaysCount(DateFrom As Date, DateTo As Date, WD As String) As Integer

'Counts Weekdays specified by 'WD' where 1 = Sunday, 2 = Monday, etc

Dim Counter As Integer

Dim i As Date

Dim d As Integer

Dim f As Integer

Counter = 0

For f = 1 To Len(WD)

d = Val(Mid(WD, f, 1))

For i = DateFrom To DateTo

If Weekday(i, vbSunday) = d Then Counter = Counter + 1

Next i

Next f

WeekdaysCount = Counter

End Function

Thanks UA

Stephen