Pay Period Total Hours from Weekly Schedule

[KrisKiel]

I am trying to have the number hours that employees work per pay period. Each day has a different number of hours and clients that we work with. Because each pay period ends on a different day of the week, I'm having trouble figuring out how to do this.

I would also like to have the number of hours employees work with each client per pay period. I have the weekly total formula for that but again, I'm having difficulty because of how pay period days change. The formula I used for the weekly client hours is:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMIF(R2:R36,"B.T.",S2:S36)

where Column R has the client names, of which B.T. is one client, and Column S is the session lengths.[/FONT]​

 

[jim may]

If you have True dates in a column (say for Example Column C) of each record....

Check out the SUMPRODUCT() function. It can be used to issue criteria if a record falls BETWEEN 2 Dates (StartDate and EndDate) which you would have to provide.

For example you could in Two Cells A1: 1/05/2019 and B1: 1/15/2019

=SUMPRODUCT((C2:C36>=A1)*(C2:C36<=B1)*(S2:S36))