Is this beyond Excel's capabilities?

[Natep]

Hi all!

I have come here several times in the past to find excellent help with my spreadsheets but I am thinking that I may be looking for something beyond the scope of Excel.

I have been asked to take over my department scheduling and I am looking for a solution that will replace the current manual input method.


I typed up a list of pertinent things that we currently use or are considered absolutely necessary, though it may not be a complete list.
I am hoping this can be a 2-workbook solution where the main workbook is simply for holding all the information of the employees, shifts and various rules and the 2nd workbook is the actual "calendar" that would be posted for the department to view each month.

Employee schedule generated by:


Pull info from a separate workbook with categories for employees by duties, assignable shifts and pre-approved vacations & holidays
Generate a year worth of 40 hour schedules with warnings/alerts when overtime may be required due to holidays/vacations/understaff
Non-consecutive weekend / holiday rule to prevent instances of any combination of consecutive weekends & holidays
Ability to add/remove employees with minimal impact to schedule for the 5 weeks following adding/removing
Allow 7-day (Thur-Wed) and 8-day (Wed-Wed) "stretches" for Alternate Work Schedule (AWS) employees
2-day rule: Employee scheduled for 2 or more consecutive days are assigned the same shift for a minimum 2 days back-to-back
Night & Day rule: Employees cannot be scheduled for shifts that begin less than 12 hours from the end of previous shift




Facility is open 24/7/365
Work week: Sun - Sat, 40 hours
Shift length: 10 hours
Full-Time Employees: ~35
Part-Time/Per Diem Employees: ~5
Daily shifts: 16
Variable shifts: 3 (Projects, Inspections, Training)

If this is within the capabilities of Excel, I would hope to find a baseline to get me started. I do my best to learn what I can to add on or complete parts on my own when possible.


Thanks,
Nate

 

[Joe4]

I don't know if Excel would be the best tool for this sort of thing, as opposed to a relational database program. But is it possible? Yes, it probably is, though it won't be easy (especially if you are not very proficient in VBA).

I would think your best bet would be to try to enlist the help of a consultant for a big project like this.
You may want to take a look here: https://www.mrexcel.com/consulting-services/

You can also Google "Excel Schedule Template", and you find many different options out there, though none will probably do exactly what you want (but maybe you can find one that can help get you started).

 

[Natep]

I don't know if Excel would be the best tool for this sort of thing, as opposed to a relational database program. But is it possible? Yes, it probably is, though it won't be easy (especially if you are not very proficient in VBA).

I would think your best bet would be to try to enlist the help of a consultant for a big project like this.
You may want to take a look here: https://www.mrexcel.com/consulting-services/

You can also Google "Excel Schedule Template", and you find many different options out there, though none will probably do exactly what you want (but maybe you can find one that can help get you started).

Thank you for the response and the suggestions.

I am definitely not an expert in VBA but I can often times find my way to an answer when I have something to work off of in the form of a template or a base-line setup that works to the most basic degree for critical parts and just needs to be expanded on.

I am hoping that I can build up a database workbook that lists all the employees with their relevant info like shifts, vacations, whether or not they are Alternate Work Schedule, etc in a easy to read and easily referenced with formulas and/or VBA. I suppose researching the best way to do this would be a great first step and maybe make my overall goal much less daunting!


Thanks again,
Nate