Finalizing schedule tab for each Staff

On the Schedule tab, could I have the weekly hour total appear whenever the day is a Saturday to show the end of the week? A line under each row that is a Saturday would  help break down the weeks as well if possible. Also, Can It show all the days within the date criteria even if they are off? if it is a day that they don't work then can it say Off in the B and C column? Also, when I re-formatted the sheet I messed up the coding for the date calendar. And finally, could the schedule dates be in long format? If this is possible that would be great!

Attachment removed by MartinLiss
Jan-29--2019-Schedule.xlsm
Max HerseyResidential SupervisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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.
Martin LissOlder than dirtCommented:
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.
Martin LissOlder than dirtCommented:
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.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Max HerseyResidential SupervisorAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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
Max HerseyResidential SupervisorAuthor Commented:
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
Martin LissOlder than dirtCommented:
How should the Long Dates be aligned? Center, Right, Left?
Martin LissOlder than dirtCommented:
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?
Martin LissOlder than dirtCommented:
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.2019-01-30_09-13-22.png
Max HerseyResidential SupervisorAuthor Commented:
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
Martin LissOlder than dirtCommented:
That graphic points out something I didn't notice before and that is that the complete date range is being shown for each house.
Martin LissOlder than dirtCommented:
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.
Max HerseyResidential SupervisorAuthor Commented:
What do you mean by formatted properly?
Martin LissOlder than dirtCommented:
"Saturday, January 05, 2019" rather than "1/5/2019""
Max HerseyResidential SupervisorAuthor Commented:
oh ok perfect thats how i wanted it anyways
Martin LissOlder than dirtCommented:
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)
Total addedrather than this. BTW this points out as I commented in this post that it would be nice if column 'B' always shows the house.
 2019-01-30_10-59-21.png
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.
Max HerseyResidential SupervisorAuthor Commented:
Capture.PNG
Max HerseyResidential SupervisorAuthor Commented:
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.
Martin LissOlder than dirtCommented:
Yes, I can do that.
Max HerseyResidential SupervisorAuthor Commented:
Capture1.PNG
Kinda like this.
Martin LissOlder than dirtCommented:
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.
Martin LissOlder than dirtCommented:
OK, please give this a try.
29133980a.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Max HerseyResidential SupervisorAuthor Commented:
The second picture was to show  how we could show when someone has 2 shifts at the same place on the same day
Martin LissOlder than dirtCommented:
Would this be OK? The colorization is just shown here for emphasis.2019-01-30_21-07-31.png
Max HerseyResidential SupervisorAuthor Commented:
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.
Max HerseyResidential SupervisorAuthor Commented:
I just looked at the file and it looks fantastic!
Martin LissOlder than dirtCommented:
Would you like a total line?
2019-01-31_08-19-10.png
Max HerseyResidential SupervisorAuthor Commented:
Yea sure Thats a nice touch
Martin LissOlder than dirtCommented:
This version includes total hours. It also shows the staff member name sooner. BTW, in this version and the previous one, I removed all the merged cells because they interfere with coding and also some builtin Excel functions. They were replaced by formatting the cells across the range. To do that you
  1. Enter text in a cell like A1. (It must be the left-most cell of the desired range)
  2. Select a range like A1:B1
  3. Format Cells|Alignment|Horizontal|Center Across Selection
  4. Click OK
29133980b.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.