Can this be done in Excel or other application

[almouchie]

basically What I am trying to do or want is

I want to optimize my labor schedule for the whole year

I have 3 shifts( A, B, C) that cover 24 hours a day.

On fridays, Satur, Sunday only 2 shifts are required
2 out of three shift need to work no less than 60 hours a week
& the other one(C) no less than 48 hours

whereby any hours above that is paid overtime per hour
Preferably the first shift starts at 7 in the morning
& each shift should get at least one day off

I have started doing Feb till May manually but I was wondering if its is feasible to be automated for the whole year in excel using VBA or other tool


Any more details I will ready to clarify more
thanks for any help

[matthewspatrick]

I have 3 shifts( A, B, C) that cover 24 hours a day.

On fridays, Satur, Sunday only 2 shifts are required
2 out of three shift need to work no less than 60 hours a week
& the other one(C) no less than 48 hours

Please clarify what happens on Fri/Sat/Sun. Do you still ned 24-hour coverage those days (in which case the assigned shifts have a longer duration than on other days), or are you allowed to scale back coverage?

Patrick

[almouchie]

thanks for ur reply Patrick

On Fri/Sat/Sun only 2 shifts will work & yes always a 24 hour shift. so on these days each shift will work 12 hours

I tried a simple formula to find out how many shifts A, B work if they have to cover 60 hours a week & the average would be 9 hours a day, & for shift C working no less than 48 hours a week it would be 6 hours a week.

Every month the shifts change so as the moring shift takes the afternoon & the afternoon takes the nite & the nite takes the moring.

Thanks

[almouchie]

I have attached what I have done so far manually
thanks

[XLGibbs]

Please do not cross post. I have posted the link to this post at Mr.Excel since you have recieved replies here. In the future please include a link to other posts at other forums as you do not want to waste anyone's time.

Thanks !

http://www.mrexcel.com/board2/viewto...=948518#948518

[almouchie]

I wanted to get as much chance of getting an answer
I didnt know same people would view different excel forums
I definitely wasnt trying to waste anyone time & I didnt know anything about cross posting.
I have been a regular in here & several other forum for a over a year & this is the first time I know about cross posting or that same people are present at different boards. This has been a very benficial place & I was more than glad for all the replies & answers I got.
Maybe If I had known about the cross posting I would have down what the link states
http://www.excelguru.ca/XLKBA/XLKBA05.htm
I would appreciated any solution I get
Thanks

[XLGibbs]

No problem almouchie, it was not an admonishment as much as a "for future reference".

I will take a look at your template and situation later today, if no one else can get it to it before me.

As far as the forum circles, many from all boards participate at other boards.

[almouchie]

Can anyone please have a look & give some feedback

[XLGibbs]

I have been working on it somewhat, although I am quite busy.

Why not just have the schedule change every 4th week instead of monthly. You could then base the applicable schedule on the weeknumber of the date in question.

Weeks 1 -4 would be A B C
Weeks 5-8 would be B C A
Weeks 9-12 would be C A B

rinse and repeat .

Just a thought.

[almouchie]

thanks for replying I appreciate it
I have a certain schedule that is used for a certain period
my whole aim is i dont want to do it manually
rather have this done automated or using vba
I will have a look again but it looks like I am going to do it manually

[XLGibbs]

My point was that you could set up a lookup table with the weeknumber and the schedule that applies.

It would have 53 rows (53 would be same as 1) with 1-53 on the left, and your schedule to the right (could be 3 columns of schedules, just alter the vlookup)

Your VLOOKUP would have something like

=VLOOKUP(weeknum(cellwithdate),LookupTable,2,0)
and could be copied down your cells where you want to return the schedule...