I'm working on a work Schedule Rotation for our crews. Each employee can work a certain amount of days and get time off.
A little about the spreadsheet. One of my coworkers showed me this spreadsheet for a gannt that I modified by adding "repeating tasks".
Column C represents the type of employee which changes the bar color. There are 2 options (limited by the Data Validation) "J":Red and "A":Blue.
Column D represents where the bars will start.
Column E represents how long each bar should be (Days working).
Column F represents how many days they get off.
Right now I've got a conditional format for every set of colored bars (total around 30) but eventually I want to simply this to one with variable range (see below).
As of now I'm using the conditional formating formula:
=AND($D8+(n-1)*$E8+(n-1)*$F8<H$4,$D8+(n)*$E8+(n-1)*$F8-1>=G$4,$C8="A")
Where n represents which bar number.
I got the chart to work (but I'd like to simplify it in the future), but now I need to be able see my man power for each of the days: I need to be able to either count the red and blue lines per day. Or if there could be a countif formula that could count the values (days employee working) from columns: C,D,E,F with the above formula with the variable "n" that varied from a set range. See attached file for details.
Bookmarks