Finalizing schedule tab for each Staff

Max Hersey
Max Hersey used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Max HerseyResidential Supervisor

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 Supervisor

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
How should the Long Dates be aligned? Center, Right, Left?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 Supervisor

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 Supervisor

Author

Commented:
What do you mean by formatted properly?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
"Saturday, January 05, 2019" rather than "1/5/2019""
Max HerseyResidential Supervisor

Author

Commented:
oh ok perfect thats how i wanted it anyways
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 Supervisor

Author

Commented:
Capture.PNG
Max HerseyResidential Supervisor

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Yes, I can do that.
Max HerseyResidential Supervisor

Author

Commented:
Capture1.PNG
Kinda like this.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
OK, please give this a try.
29133980a.xlsm
Max HerseyResidential Supervisor

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

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 Supervisor

Author

Commented:
I just looked at the file and it looks fantastic!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Would you like a total line?
2019-01-31_08-19-10.png
Max HerseyResidential Supervisor

Author

Commented:
Yea sure Thats a nice touch
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial