Employee breakdown by half hour on rotating 6 week schedule

Good Afternoon,

This is a bit of a long request for help, but I will try my best to include all information. I have a 6 week work schedule for 350 people. It rotates every 6 weeks because each person has a (1) scheduled training day in those 6 weeks. What I am trying to compile would be a daily wrap up of how many people are on shift (not including people in training or of course on days off), by half hour, and then do that for each day of the rotation. Attached is the information that I have to work with. I have flirted around with IF, AND, and SUMIF statements, but I think this one will be a lot more complex than just those. As always, I will help to answer any question that you may have.

is this what you want? or at least in the right track?

Headed in the right direction, but not quite what I'm looking for. I need not just how many are scheduled for that day, but a breakdown of how many people are on duty for each half hour. In the example shown, that would be from 0400 to 1200 by half hour increments.

I wrote a macro and added the half hours labels down the sheet.

That looks great. The only thing I failed to provide in the original post is to have it also broken down by location. So you would have each location broken down by half hour with the amount of available employees. Other than that, rcm, you are right on track. Thanks.

The top of each hour in the breakdown section only shows 1 and not the true number of employees on shift. Is that just an adjustment in the VBA?

Give it a run

1. I added the location so there are half hour intervals by location.
2. The counting is done by the starting time of the interval.

Originally Posted by rcm

Give it a run

1. I added the location so there are half hour intervals by location.
2. The counting is done by the starting time of the interval.

I have stumbled across a few shifts that start at 45 after and end at 15 after. Is there anyway to build in to the code to have those shifts rounded up 15 minutes so that they fall into a 30 minute increment? Other than that, i believe it is exactly what I'm looking for.

Dear aferguson:

let me try to understand the rounding procedure so it can be programmed...

0445 -> 0500 ?
1215 -> 1230?

so

..45 should be added 15 minutes to the next hour
..15 should be added 15 minutes to the next half hour

Originally Posted by rcm

Dear aferguson:

let me try to understand the rounding procedure so it can be programmed...

0445 -> 0500 ?
1215 -> 1230?

so

..45 should be added 15 minutes to the next hour
..15 should be added 15 minutes to the next half hour

Yes, that would be correct.

I added some code to handdle the 15 minutes adjustments. I changed the input and highlighted the changes to test the code.

Originally Posted by rcm

I added some code to handdle the 15 minutes adjustments. I changed the input and highlighted the changes to test the code.

So i actually now need any start time that is 15 or 45 to round up, while any end time that is 15 or 45 to round down. I've looked at the vba but can't seem to adjust it properly.

And also, I've noticed that it counts someone as being on shift at their end time. Example, if they get off at 1230, it's showing 1 when it should show 0.

I am sending the macro with the changes highlighted for you to change them

Now, if the ending time is 15 it rounds it off to the hour
if the ending time is 45 it rounds it off to the half hour (30)

in the second modification the counting loop stops short of the ending hour

Please Login or Register  to view this content.

Originally Posted by rcm

I am sending the macro with the changes highlighted for you to change them

Now, if the ending time is 15 it rounds it off to the hour
if the ending time is 45 it rounds it off to the half hour (30)

in the second modification the counting loop stops short of the ending hour

Please Login or Register  to view this content.

So i made the changes, but look at the attached and see how it handles the highlighted shift. It just fully ignore the shift.

you are right, since I copied from the previous block (the h1t) I did not change some of the h1t references, here is the correct code

Please Login or Register  to view this content.

What would I need to change in the code to have my master schedule on sheet 1 and the half hour breakdown to compile on sheet 2 starting in cell A5?

I thought you never asked!

Please Login or Register  to view this content.

You rock, rcm. I will mess around with it tomorrow, so stand by to possibly answer a few more noob questions.

Originally Posted by aferguson

You rock, rcm. I will mess around with it tomorrow, so stand by to possibly answer a few more noob questions.

What if on the sheet 2 i want a cell that i enter the date that i want the code to run for? So if i want to make adjustments in the schedule for one date, i can just update that specific date. Does that make sense?

Originally Posted by rcm

I thought you never asked!

Please Login or Register  to view this content.

What if on the sheet 2 i want a cell that i enter the date that i want the code to run for? So if i want to make adjustments in the schedule for one date, i can just update that specific date. Does that make sense?

I just realize that there is a lot more adjusting needed wait a while. It dawn on me that the table below has to be adjusted so after the first run, any changes in the schedules has to be monitored to keep it updated.

Originally Posted by rcm

I just realize that there is a lot more adjusting needed wait a while. It dawn on me that the table below has to be adjusted so after the first run, any changes in the schedules has to be monitored to keep it updated.

So it could be done? But more adjustments would have to be made than in your last edit?

yes it can be done. I see to it.

Here it is. It cleans to null the desire column(s) within the range given by A1 and B1.
It colors the range in both sheet1 and sheet2 (if you don't want it eliminate the lines that contain "interior.color"
It recounts just for that range

Please Login or Register  to view this content.

there was a glitch on the marked line
for some reason the sort is not working properly, but when applied manually it does work!!

Please Login or Register  to view this content.

Originally Posted by rcm

there was a glitch on the marked line
for some reason the sort is not working properly, but when applied manually it does work!!

Please Login or Register  to view this content.

That looks great. Which sh1's do i need to change to have the date selection on sh2?

Please Login or Register  to view this content.