Avatar of Max Hersey
Max Hersey
Flag for Canada asked on

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
Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

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 Liss

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 Liss

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Max Hersey

ASKER
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 Liss

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 Hersey

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

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

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 Liss

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Max Hersey

ASKER
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 Liss

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 Liss

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Max Hersey

ASKER
What do you mean by formatted properly?
Martin Liss

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

ASKER
oh ok perfect thats how i wanted it anyways
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Martin Liss

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 Hersey

ASKER
Capture.PNG
Max Hersey

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

Yes, I can do that.
Max Hersey

ASKER
Capture1.PNG
Kinda like this.
Martin Liss

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Max Hersey

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

Would this be OK? The colorization is just shown here for emphasis.2019-01-30_21-07-31.png
Max Hersey

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Max Hersey

ASKER
I just looked at the file and it looks fantastic!
Martin Liss

Would you like a total line?
2019-01-31_08-19-10.png
Max Hersey

ASKER
Yea sure Thats a nice touch
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.