Finalizing schedule tab for each Staff

On the Schedule sheet do you want 'Staff Name' replaced by the current staff member's name? And are you aware that the posted workbook contains real employee names and email addresses? Also parts of the code won't work because in the Staff sheet, the names in column 'A' don't match those in AG, AI, AK and AM.

I suggest you replace the workbook that you posted with one that's obfuscated.

In addition to my question and comments above, you say

have the weekly hour total appear whenever the day is a Saturday

and I'm confused because when I look at the Staff sheet it looks to me like your weeks start on Tuesday and end on Monday.

And thirdly, please take a look at the attached workbook and manually modify it to show me what the output should look like (ignore the weekly hours for now) if the employee did not work on the 10th and 11th.

Ok I just added a new workbook in. I changed staff names and the pivot tables need to be refreshed before the names will change. However, because column locations have changed in the past, those Pivot table will have to be remade. With my new passwords, I couldnt get your coding to work so I just did a manual presentation of the the schedule tab would look like. Our pay weeks are Sunday to Saturday so this is why it would be good to see the hours. I understand how the start of the days are going to make it an issue, Just means that the start of each workbook needs to be a Sunday on the house tabs.

Try this using Employee1 (password = "xxx"). If it's what you want then please close this question and if you approve I'll add two things.

• If the last day of the selected date range isn't a Saturday then I'll calculate the "Weekly" hours for that partial week. Alternatively I could require that the 'Shifts To Date' selection be a Saturday.
• Add a "Grand Total" at the bottom.

29133980.xlsm

i attached the file that I had tried the schedule out with. This is the right idea but It doesn't seem to be working properly for when the end of the week is. Also, it is populating dates that are past the date threshold. it would be good if the dates are in Long Date Format so it shows the day of the week as well. As you said up there, it may be good if the start date had to be a Sunday and the end day had to be a Saturday. that would make things nice and clean.
Jan-30--2019.xlsm

How should the Long Dates be aligned? Center, Right, Left?

It doesn't seem to be working properly for when the end of the week is

Every Saturday is underlined. Isn't that what you want?

Also, it is populating dates that are past the date threshold.

I don't see that happening.

Also you requested that when a shift isn't worked that both columns 'B' and 'C' be "OFF". If it were up to me I wouldn't change column 'B' because when you do you get this where it's the next house but there's no indication of which house.

Did you see my workbook? some  weeks only had 6 days in them and others had 8. so im not sure why it was doing that

That graphic points out something I didn't notice before and that is that the complete date range is being shown for each house.

Did you see my workbook?

I think what you are seeing is the result of the dates not being formatted properly, because after I do that they all have 7 days.

What do you mean by formatted properly?

"Saturday, January 05, 2019" rather than "1/5/2019""

oh ok perfect thats how i wanted it anyways

That graphic points out something I didn't notice before and that is that the complete date range is being shown for each house.

The date range is being repeated because it searches each house for the same range and I don't see a way around that, so I think there are two ways we can go:
1) Keep the current structure where columns 'B' to "D' show all the houses, but add a total line at the last date of the date range. In other words when for example the date range is 1/1 to 1/8 you would see this (note the line at row 13)
rather than this. BTW this points out as I commented in this post that it would be nice if column 'B' always shows the house.
 
2) The other way to do it would be to have separate columns for each house, where 'B' to 'D' would be for Maison Pacific and 'E' to "G' would be for Pinegarry, etc.

Is this possible? Also, we will have to combine the 6am-9am and 4pm-9pm positions because this will create issues in both the AFL tab and the schedule tab. Unless you can make it so that it adds both shifts to a specific row? For example if on Feb 13 employee 1 was scheduled for both 6am-9am and 4pm-9pm at Maison Pacific, it would have two rows for that date. If that is possible, then that would be ideal.

Yes, I can do that.

Kinda like this.

I don't know what you are showing me in your last picture, but unless you object I will do side by side houses like in the picture before that.

The second picture was to show  how we could show when someone has 2 shifts at the same place on the same day

Would this be OK? The colorization is just shown here for emphasis.

Yea if that works then that would be great. However maybe we need some sort of control so that staff cant schedule for the same shift times. In that example, it overlaps for 1 hour at 8am to 9am.

I just looked at the file and it looks fantastic!

Would you like a total line?

Yea sure Thats a nice touch