Pay Period Total Hours from Weekly Schedule

KrisKiel

New Member
Joined
Feb 16, 2019
Messages
26
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]
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
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

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

Watch MrExcel Video

Forum statistics

Threads
1,099,521
Messages
5,469,136
Members
406,638
Latest member
Jack_Johnson

This Week's Hot Topics

Top