# of Hrs per Date Range to a Week Num

Hi All,

I've worksheet that I use to track employee, weekday and # of Hr per day. it looks like this:

Name 1/1/17 1/2/17 1/3/17 1/4/17 1/5/17 ..........1/15/17 1/16/17 1/17/17 ....( this continues until 12/31/17)
JSMith 0 7 7 7 7 0 0 0
JDoe 4 4 4 4 0 0 0 0
JSMith 3 3 3 3 3 0 0 0
JDoe 0 0 0 0 0 4 4 4


What I am trying to accomplish is:

1) not exceed 40 Hrs per week
2) have a way to see the weeks with more than 40 hrs by employee

The weekday info is extracted from the employee Start Date and End Date and number of Hours planned to work on that range. i.e:

Name Start Date End Date # of Hours
JSmith 1/2/17 1/5/17 7
JDoe 1/1/17 1/4/17 4
JSmith 1/1/17 1/5/17 3
JDoe 1/15/17 1/17/17 4

I took the range and break everything by day, the reason is the employee can have multiple assignments on the same date range if the number of hours doesn't exceed 8 hrs per day. Breaking this by day, I can see the total number of hours allocated for each user each day and avoid Over Time.

Now, what I am having problems is looking at the total per week instead of per day, as when coordinating schedules we need to know what weeks are "better" than the other ones. if each employee is allowed to work only 8hrs per day, a Week should be 40Hrs. When planning the schedules, a week that doesn't have the 40 Hrs already for the employee is better than the ones that he already have the 40Hrs scheduled. I use the =WEEKNUM() to get the week # for the date, but having problems on getting all the hrs schedule for that week based on the date range for the employee.

The ideal output will be something like:

Name Week 1 Week 2 Week 3
JSmith 43 0 0
JDoe 16 0 12

And with that info, it can be added to the main schedule tab:

Name Start Date End Date # of Hours Availability
JSmith 1/2/17 1/5/17 7 No Avail. ( the range is for Week1, which is already on 43)
JDoe 1/1/17 1/4/17 4 Avail.
JSmith 1/1/17 1/5/17 3 No Avail.
JDoe 1/15/17 1/17/17 4 Avail.

Any help would be really appreciated!

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.