Staff Scheduler

Hi,

I'm trying to make a scheduler that will count the number of staff in a given hour, including those who fall in overlapping schedules. Sheet1 will calculate the number of staff in a given 1 hour interval whose schedule is listed in sheet2. if it can be done in VBA, it would be much better. Please help.

Hi Baldowsky

I am trying to think of a way of doing this (but don't let that stop anyone else finding a solution!)

How do you want part hours shown? If someone works 4:30 - 13:30 will that person appear in both the time slot commencing 4:00 and the one commencing 13:00? (they will then be in 10 hourly slots for 9 hours worked). An alternative would be to only record them if they are present at the beginning (or the end) of a time slot.

A more logical way might be to have the time slots starting on the half hour.

Regards
Alastair

Originally Posted by aydeegee

Hi Baldowsky

I am trying to think of a way of doing this (but don't let that stop anyone else finding a solution!)

How do you want part hours shown? If someone works 4:30 - 13:30 will that person appear in both the time slot commencing 4:00 and the one commencing 13:00? (they will then be in 10 hourly slots for 9 hours worked). An alternative would be to only record them if they are present at the beginning (or the end) of a time slot.

A more logical way might be to have the time slots starting on the half hour.

Regards
Alastair

I have to agree with what you said, it is more logical to have a half hour interval.

Hi Baldowsky

Sorry for the delay in getting back to you - I have had some hardware problems - but all is now well.

I looked at the solution and decided to do my own thing! It is often easier.

Working of having the schedule based on the hour (rather than our agreed more logical half hour) I have worked on the assumption that if the person works 4:30 - 13:30 the will not appear in the 4:00 starting slot (because they were not there at the beginning of the time slot) but will appear in the 13:00 time slot. (Note that this does give an interesting result for those starting at 23:30 as they are not recorded until the next day.

I did not understand your comment

(ex. Sunday evening to Monday morning)

so I ignored it . Your example shows Saturdays and Sundays - so I have.

You will note that I have run a check (in green) to ensure that all hours are carried across.

Let me know if this works for you.

Regards
Alastair

Hi Alastair,

This is awsome. thanks a lot.