Populate shift pattern on date rows

Dave Schierman

Hi all. We have a sheet with column named “Employee” and “Shift” followed by a string of columns with dates to make a schedule. Each cell will either be blank if the EE is not working or will contain a number that reflects where they will be working.  I’m trying to find a way to pre populate the cells in a repeating pattern based on what “shift” they are assigned.  We have wide variety of shifts such as Mon, Tue, Wed every other Thur, Day on , day off for 4 days then 4 days off, etc Was thinking I could have a list of shift patterns with a referenced “start date” of the pattern then pull off of that to populate cells, but am stuck on how to proceed. Any assistance would be greatly appreciated.

Genevieve P.

Hi @Dave Schierman

The only way I can think to do this with your current sheet structure is to create multiple column formulas for each of your date columns. Each column would have to have its own unique formula though, so it would be a lot of manual work to create each formula, and you would need to create a new one each time you add in a new column/date.

I would actually suggest a different sheet structure, where there is only one Date Column with every date populated, and each employee has their own column to indicate which days they are on shift (versus a row).

If you have it set up this way, you can create helper columns with formulas to find the WeekDay and if the Week of the Year is Odd or Even.

You can hide these helper columns. They will enable you to create Change Cell Workflows for each schedule type, auto-filling in if someone is due to be on shift based on the dates in the Date column.

(See: Change the Value of a Cell in an Automated Workflow)

Then we can use the WeekNumber column (Odd or Even) to set up a workflow for someone who has an "every-other" type of shift. You can also specify a Start Date by adding it in the Condition of the workflow.

(See: Condition Blocks: Filter What Your Automated Workflows Send)

The only shift patter that is trickier is the 4 on - 4 off type of shift. In this instance, I would actually suggest just manually typing in the "On Shift" text and using drag-fill to create a pattern. If you select 4 cells that say "On Shift" and 4 that are blank, you can drag this down to continue the pattern for the whole column.

(See: Create a Series of Numbers or Dates with Drag-Fill)

Dragged down:

Here are the formulas I used in the WeekNumber and WeekDay columns:

WeekNumber

=IFERROR(IF(ISEVEN(WEEKNUMBER(Dates@row)), "Even", "Odd"), "")

WeekDay

=IFERROR(WEEKDAY(Dates@row), "")

If I've misunderstood what you're looking to do, I would recommend reaching out to your company's Customer Success representative (if you have one) to discuss best practices and sheet set-up.

Cheers!

Genevieve