Glad we could help, Joseph! Though I'm still not sure why you have the "on error resume next" in there, I'm just going to assume you need it for some reason.
I made a little adjustment to your code, just to shorten it a little bit and remove the bit If block. Should have the same functionality, however:

Function Productivity(ByVal date1 As Range, ByVal name1 As Range) As Long
     'Application.Volatile
    On Error Resume Next
Dim dateRng As Range
    Dim nameRng As Range
    Dim RG1 As Range
    Dim RG2 As Range
    Dim WS As Range
    Dim Total As Long
    Const TableDate As Date = #6/25/2005#
Total = 0
For Each WS In Range("Employees").Cells
If date1 <= TableDate Then
            Set RG1 = Sheets(WS.Text).Range("A4:A32")
            Set RG2 = Sheets(WS.Text).Range("B1:HA1")
        Else
            Set RG1 = Sheets(WS.Text).Range("A37:A65")
            Set RG2 = Sheets(WS.Text).Range("B34:HB34")
        End If
For Each Cell In RG1.Cells
            If Cell.Value = name1.Value Then
                Set nameRng = Cell
            End If
        Next Cell
For Each Cell In RG2.Cells
            If Cell.Value = date1.Value Then
                Set dateRng = Cell
            End If
        Next Cell
If Not nameRng Is Nothing And Not dateRng Is Nothing Then
            Total = Total + Intersect(dateRng.EntireColumn, nameRng.EntireRow).Value
        End If
Next WS
Productivity = Total
End Function
Matt