I have a crew schedule made up in excel. There are 4 different shifts A B C or D. There are 5 people on the schedule, and the schedule is laid out like this in excel:


Each person works the same schedule weekly, so it always looks like this for each given weekday.

I have figured out how to make the dates run consecutively in column A based upon the date entered manually in cell A4, and the day of the week in column B be based off the date to the left of that box in column A; all of that works great, and I made a separate spreadsheet for leap years.

My Question is this: Is there a way to make each person's shift be automatically entered in columns C through G based on the day of the week in column B in the row?

I currently have to manually type out everything in columns C through G and it is time consuming to say the least. My ultimate goal is when I input the date in A4 everything else will update automatically.

  • Just curious thought; if the schedule is the same every week, why go to the complicated effort of a dated schedule? If its the same each day of the week, the date won't matter. Right? – CharlieRB Sep 8 '14 at 12:30
  • The reason is for long range planning of vacation/sick days. We cover our off time amoungst ourselves and this helps us plan ahead. – Dave Bennett Sep 9 '14 at 8:45

A simple way to do this is to create an admin table to store your basic schedule. Put it on a separate 'lookup' tab like this. In the left column enter days 1-7. Here I've used the Excel default of 1=Sunday - you may wish to make 1=Monday (more on that later.)

enter image description here

Now in our main table we perform a VLOOKUP() against our admin table to pull in the appropriate schedule in each column.

In cell C2 enter:

         WEEKDAY($A2)            -- converts date to a number 1-7
        ,lookup!$A$1:$F$8        -- your admin table
        ,COLUMN()-1              -- column of the admin table to use
        ,0)                      -- exact matches only

and copy down and across for all rows & columns.

enter image description here

If you want 1=Monday then adjust the formula slightly:

         WEEKDAY($A2,2)       -- add an extra ,2
| improve this answer | |
  • I do not have enough rep yet, so I can not vote, but this worked perfectly, and it achieved my goal of only having to input the date in cell A4 (I had to change the lookup formula to A4 because that is the cell where I have the spreadsheet starting in). Thank you very much for the help. – Dave Bennett Sep 9 '14 at 9:01
  • Glad it helped you :) – Andi Mohr Sep 9 '14 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.