Hi Andrew,
Hopefullythis will get you moving in the right direction.
The first thing to do is create a table for holidays. Name it "tblHolidays".
The fields are "HolidayDesc" type Text and "HolidayDate" type Date. Save it
and add the holiday dates - the desc is optional.
I don't know what the rest of your code looks like, so I created a function.
Put it in a standard module. I named the function
Public Function CalcPay(pHrRate As Currency, _
pBeg As Date, pEnd As Date) As Currency
It has 3 parameters :
pHrRate - the hourly pay
pBeg - the shift start date/time
pEnd - the shift end date/time
You would call it like this:
ShiftPay = CalcPay(HourlyRate, ShiftStart, ShiftEnd)
(HourlyRate, ShiftStart, ShiftEnd are (could be) variables in your code)
I didn't know if a nurse would have a different rate of pay at different
hospitals.
So I first check if the date is a holiday, if it is, check if days or nights.
If it is not a holiday, then check if weekend, then check for days of nights.
Add the hours for each, multiply by the premium rate (converts overtime to
straight time), gets the total hours and multiplies by the hourly rate.
You will need the check holiday function also (included).
----------------------------------------------------------------------
WARNING - this is largely untested. You've been warned!!!!
----------------------------------------------------------------------
Here are the functions:
'--------------------------------------------------
Public Function CalcPay(pHrRate As Currency, _
pBeg As Date, pEnd As Date) As Currency
On Error GoTo ErrorHandler
Dim HrsWorked As Integer
Dim i As Integer 'counter
Dim tmpDate As Date
Dim tmpTime As Date
Dim DayStart As Date
Dim DayEnd As Date
Dim wDay As Integer
Dim WeekdayDayHrs As Single
Dim WeekdayNightHrs As Single
Dim SatDayHrs As Single
Dim SatNightHrs As Single
Dim SunDayHrs As Single
Dim SunNightHrs As Single
Dim HolDayHrs As Single
Dim HolNightHrs As Single
Dim totSTHrs As Single
Dim IsHoliday As Boolean
Dim WeekdayDayRate As Single
Dim WeekdayNightRate As Single
Dim SatDayRate As Single
Dim SatNightRate As Single
Dim SunDayRate As Single
Dim SunNightRate As Single
Dim HolDayRate As Single
Dim HolNightRate As Single
'---------initialize variables---------
WeekdayDayHrs = 0
WeekdayNightHrs = 0
SatDayHrs = 0
SatNightHrs = 0
SunDayHrs = 0
SunNightHrs = 0
HolDayHrs = 0
HolNightHrs = 0
IsHoliday = False
'**************************
' You need to set these to the proper numbers
'these are used to convert premium hrs to ST hrs
WeekdayDayPrem = 1
WeekdayNightPrem = 1.5
SatDayPrem = 2
SatNightPrem = 2.5
SunDayPrem = 2.5
SunNightPrem = 2.5
HolDayPrem = 3
HolNightPrem = 3
DayStart = #8:00:00 AM#
DayEnd = #4:59:00 PM#
'**************************
'---------end initialize variables---------
'get total hrs worked
HrsWorked = DateDiff("h", pBeg, pEnd) - 1
'evaluate each hour
For i = 0 To HrsWorked
tmpDate = DateAdd("h", i, pBeg)
'check for holiday
' might need more logic as to when the holiday
' pay starts and ends.
' ie 12/25/ is a holiday.
' If you start work at 6pm, should you get holiday
' pay only until midnight or until shift end on
'12/26 at 6 AM?
IsHoliday = checkForHoliday(tmpDate)
' adds 1 hour to the appropriate variable for days or nights
If IsHoliday Then 'Holiday
Select Case tmpTime
Case DayStart To DayEnd 'days
HolDayHrs = HolDayHrs + 1
Case Else 'nights
HolNightHrs = HolNightHrs + 1
End Select
Else 'not holiday
wDay = Weekday(tmpDate)
tmpTime = TimeValue(tmpDate)
Select Case wDay 'test for weekday, Sat, Sun
Case vbSunday
Select Case tmpTime
Case DayStart To DayEnd 'days
SunDayHrs = SunDayHrs + 1
Case Else
SunNightHrs = SunNightHrs + 1
End Select
Case vbSaturday
Select Case tmpTime
Case DayStart To DayEnd 'days
SatDayHrs = SatDayHrs + 1
Case Else
SatNightHrs = SatNightHrs + 1
End Select
Case Else ' its a weekday
Select Case tmpTime
Case DayStart To DayEnd 'days
WeekdayDayHrs = WeekdayDayHrs + 1
Case Else
WeekdayNightHrs = WeekdayNightHrs + 1
End Select
End Select
End If
Next
' calc equivelent ST hours
totSTHrs = totSTHrs + (WeekdayDayHrs * WeekdayDayPrem)
totSTHrs = totSTHrs + (WeekdayNightHrs * WeekdayNightPrem)
totSTHrs = totSTHrs + (SatDayHrs * SatDayPrem)
totSTHrs = totSTHrs + (SatNightHrs * SatNightPrem)
totSTHrs = totSTHrs + (SunDayHrs * SunDayPrem)
totSTHrs = totSTHrs + (SunNightHrs * SunNightPrem)
totSTHrs = totSTHrs + (HolDayHrs * HolDayPrem)
totSTHrs = totSTHrs + (HolNightHrs * HolNightPrem)
' calc total pay for shift
CalcPay = totSTHrs * pHrRate
Exit_Point:
Exit Function
ErrorHandler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Point
End Function
'###
Public Function checkForHoliday(dWkDate As Date) As Boolean
Dim DB As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Set DB = CurrentDb
sSQL = "SELECT [HolidayDate] FROM tblHolidays"
sSQL = sSQL & " WHERE [HolidayDate] = #" & dWkDate & "#;"
Set rst = DB.OpenRecordset(sSQL, dbOpenSnapshot)
checkForHoliday = False
If Not rst.BOF And Not rst.EOF Then
checkForHoliday = True
End If
rst.Close
Set rst = Nothing
Set DB = Nothing
End Function
'--------------------------------------------------
HTH
