Hello All,
I am hoping one of you gurus have a solution in my case. I have need to create a rotation schedule. I am having difficulty trying to figure out how to do this with my limited knowledge. I have a list with Names for a crew of up to 200 guys in rows in column A, a Start date in column B, an End date in column C, a schedule ON shift in days in column D, and a schedule OFF time in days in Column E then a group of cells in columns to the right beginning from a sheets rotation schedule reference date that is manually updated for a given day in the year. So the values in the cells could be a hidden date reference or whatever makes sense for conditional formatting or VBA if that's the best approach. All of the start and end dates would be near term and should be after the rotation schedule reference date by design so they would show up on the schedule. I need enough cells to the right to populate for a years worth of days from the rotation schedule reference date. I want to be able to take all of the cells between the start and end date and shade them in each row then when the last cell or end date cell is complete I want to use the column E schedule OFF time and skip that many days and then begin shading the next cell for the number of ON days from column D and then keep going until those days are complete and then begin the schedule OFF again until done then repeat ON and OFF then ON and OFF until complete to the end of the years worth of cells. Does this make sense? I am taking a persons first schedule which might not be an exact match of ON days and then projecting what it would be once ended based on a number of days cycle entered. Probably a drop list maybe. For example 28 days ON and 14 days OFF is most typical in my environment. It doesn't appear to be too complicated except until I try to wrap my head around how to do it but I have trouble with simple things. I was not sure if could be done without VBA which I'm not savy with at all. I can use a lot of formulas but nesting all the functional requirements to satisfy this has me unable to break it down in conditional formatting. Although maybe it will need to be VBA so in that case a button maybe to refresh and go from top to bottom in the name list until there's no more makes more sense. I would greatly appreciate your help.
I have included an attachment that is more or less like what I described above but columns are different because of what I actually have from a short term Gantt chart now. I will have an On-Return date but it's not consistently available so I didn't plan to use it. I will need to start the OFF pattern first then the ON pattern as a rule. If I had something for the above description I think I can figure out how to change the references to match my attachment or whatever makes sense for the contributor or guru.
Thank you in advance
Bookmarks