How to Create Shift Roster 24×7 with Excel Automation


Step 1 – Define Names for the Dropdown List

  • Create dropdown boxes for 3 different items: Month, Year, and Shift.

Let’s say we have five shifts from morning to night. M1, M2 represent morning shifts. A is for afternoon, and N1, N2 are for night shifts. There are also two off days defined by O1 and O2. Each employee has to work one shift each day, except for off days. Let’s define the names for the months with the help of Name Manager.

Dataset for dropdown box settings

  • Copy the range B5:B16.
  • Select Define Name from the Formulas tab.

showing steps to define name for a data range

  • A window will open where you have to define a name for range B5:B16. We named it Settings_Month.
  • Press OK.

Assigning name for months

  • Similarly, define the name for the Year. We named it Settings_Year.

Assigning name for year

  • Define the Shift Type elements in the same procedure. We named it Settings_Shift_Legend.

Assigning name for shift legend.

You can see all the names that have been defined by clicking the Name Manager option.

Name Manager location.

  • If we click the Name Manager option, a window like this will open where you can see all the defined names.

Showing 3 names for different data ranges.

Step 2 – Create Dropdown Lists with Data Validation

This is the outline of our Shift Roster. We will insert the Month and Year names in the dropdown boxes. Also, we need a dropdown box of Shift legends in each cell of the First Shift column. To create drop-down list, we’re going to use the Data Validation tool of Excel.

This Shift Roster can technically start anywhere in the month (e.g. 15 January – 14 February). You can set Month Start. This will automatically update the Start Date and End Date. We can set the First Shift. And the Roster will automatically update.

Dataset overview for the shift roster.

We have to create two dropdown boxes to specify which month of the year we are working with.

  • Select the merged cell J4.
  • Select Data Validation from the Data tab.

Steps to create dropdown boxes from Data Validation

  • A Data Validation will open to import the data source.
  • Select List from the Allow dropdown box.
  • Click on the Source box and press F3.

Assigning source data

  • A window named Paste Name will appear. You can see all the defined range names.
  • Select Settings_Month and press OK.

selecting proper data name

  • We can see Settings_Month in the Source box of the Data Validation window.
  • Now press OK.

completing the data validation process

  • Select the merged cell J4.
  • You will see a dropdown arrow on the right side of the merged cell. You can select any month from there.

showing dropdown box for month

  • Similarly, create a dropdown box in the merged cell M4 for Year.

showing dropdown box for year

Now, we have to create dropdown boxes for each cell in the First Shift column.

  • Select all the cells from C8:C17.
  • Import the defined name Settings_Shift_Legend through Data Validation as shown earlier.

steps for inserting dropdown boxes in each cell of the First Shift column

Step 3 – Insert Formula for Relevant Fields

Let’s say we want to start at the beginning of the month:

  • Write 1 in the Month Start.
  • Write the following formula in the Start Date box and press Enter.

This formula takes values from three cells and gives values in date.

  • The start date will fill in. You might have to change the format to a date.

formula for start date

  • Copy the following formula to the End Date box and press Enter.
=EOMONTH(I5,0) + E5 -1

formula for end date

  • Write =E5 in the E7 cell and press Enter. You will get “01” as the starting date.

 inserting first date

  • Write the following formula in F7 and press Enter.
= IF(E7>=$P$5,"",E7+1)

This formula gives sequential dates up to the End Date.

  • Then hold and drag the F7 cell rightward to get all the dates.

formula for creating dates

  • By doing so, we have dates for the whole month.

showing image after all the date fields are filled

  • Write the following formula in E6 and press Enter.

=TEXT(E7, "ddd")

This formula converts the dates into weekdays.

  • Hold and drag the E6 cell rightward to get all weekdays.

 formula for weekdays

  • Thereby, we get all weekdays. Modify the cell formatting so the text is rotated (Home > Orientation > Rotate Text Up).

showing images that all week days have been filled

Step 4 – Find Shift Roster for All Employees

  • Select a First Shift.

selecting first shift

  • Write the following formula in E8 and press Enter.
=IF(OR($C8="",E$7=""), "",IF(D8= "",C8, INDEX(Settings_Shift_Legend,IF(MATCH(D8,Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend),1,MATCH(D8,Settings_Shift_Legend,0)+1))))

This formula checks the First Shift cell($C8) and the upper date cell(E$7) if they both are blank. When both of them are blank, the formula returns an empty string.

If not, it checks if the previous shift cell(D8) is empty. If D8 is not empty it finds the value of D8 in the “Setting_Shift_Legend” range and returns the next shift legend.

It returns the value of C8 if D8 is empty.

In short, this formula gives sequential Shifts for each employee.

This will give you M1.

  • Hold and drag the first result cell to the right to get Shift Roster for the first employee.

Formula for creating shift roster

  • Then hold and drag the entire selected range downwards to copy the formula from E8 to AI17.

Copying formula in all rows

  • For the second employee’s First Shift, select M2.

And the second row will be filled with Shift legends indicating the employee’s shift on each date.

showing second employees shift roster

  • Select the First Shift for all other employees and the worksheet will automatically give their Shift Roster.

showing shift roster for all employees

Step 5 – Shift Roster for Different Month

You can create a Shift Roster for different months by just changing the Month and Year.

  • From the dropdown boxes, change the month to February and the Year to 2020.

And automatically, the spreadsheet will give the Shift Roster for February 2020.

This month has 29 days, and it has been modified accordingly.

shift roster for a different month

Read More: How to Create Monthly Duty Roster Format in Excel

Frequently Asked Questions

1. What information should be included in a shift roster?

A shift roster can include the name of the employee, the date, the start and end time of the shift, the type of shift (e.g. morning, afternoon, night), and any notes or comments (e.g. overtime, training).

2. Can I use Excel to generate automatic alerts or reminders for shift changes?

Yes. You can use VBA code or conditional formatting. For example, you can assign a rule that highlights shifts that have been changed or send an email notification to affected employees.

3. Can I use Excel to track employee attendance or time off?

Yes, you can track employee attendance or time off by adding columns or sheets to the roster and you can use formulas or functions to calculate the total hours worked or missed. You can also use conditional formatting or data validation to indicate absences or holidays.

4. Can I use Excel to create a visual representation of the shift roster?

Yes, you can use Excel to create a visual representation of the shift roster with charts or graphs. For example, you can create a Gantt chart to show the duration and overlap of shifts or a pie chart to show the distribution of shifts by type.

5. How can I protect the shift roster from accidental or intentional changes?

You can protect the shift roster using Excel’s protection features such as password protection, worksheet protection, or workbook protection from accidental or intentional changes. You can also restrict specific cells or ranges from editing or formatting.

Things to Remember

  • You should ensure proper formatting for the start and end dates.
  • Mention all relevant information about your Shift Roster.
  • You can use conditional formatting to highlight any shift that has special characteristics.
  • You should check your roster properly before using it.

Download Practice Workbook

You can download this practice workbook.

Related Articles

<< Go Back to Roaster Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

  1. excellent, i want to create a roaster include D1 D2 DN N1 N2 O. This one must rotate full month and continue next month.
    for example in January 1st can come dn and february 1st o.
    can u create a formula for this?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jan 18, 2024 at 9:54 AM

      Hello TH

      Thanks for reaching out and sharing your requirements. You wanted a roaster including D1, D2, DN, N1, N2, and O to rotate full month and continue next month.

      I am delighted to inform you that I have created a roaster to fulfil your goal. So, follow these:

      1. Change the Shift Type based on your need.

      2. Modify the named range for year values.

      3. Select cell P5 and apply the following formula: =EOMONTH(I5,R4) + E5 -1

      4. Select cell F7, insert the formula: =IF(E7>=$P$5,"",E7+1) And drag the Fill Handle icon to copy the formula down.

      5. Select cell E6, insert the formula: =TEXT(F7, "ddd") And drag the Fill Handle icon to copy the formula down.

      6. Select cell E8, insert the formula: =IF(OR($C8="",E$7=""), "",IF(D8= "",C8, INDEX(Settings_Shift_Legend, IF(MATCH(D8,Settings_Shift_Legend,0)+1>COUNTA(Settings_Shift_Legend), 1,MATCH(D8,Settings_Shift_Legend,0)+1))))
      And drag the Fill Handle icon to copy the formula.

      Return to the sheet, and use the file like the following GIF.

      Hopefully, the idea will work for you. I am attaching the solution workbook for better understanding. Good luck.

      Lutfor Rahman Shimanto

  2. Hi Team,

    Thanks for the Excel. I need to create a shift for 4 Members Team. The shifts are

    Shift A: Monday to Friday- Day shift ( Sat and Sun Week Off)
    Shift B: Monday to Friday- Night shift ( Sat and Sun Week Off)
    Shift C: Monday ,Tuesday, Wednesday, Saturday, Sunday – Day Shift ( Thursday , Friday Week off)
    Shift D: Wednesday , Thursday, Friday – Day Shift , Saturday, Sunday – Night Shift ( Monday and Tuesday Week off)

    On Wednesdays All 4 members will Work in 3 persons in Day shift and 1 person in night shift.

    Can you please help to create this shift scheduler.


    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 7, 2024 at 4:26 PM

      Hi Karthick

      Thank you for reaching out with your inquiry! Designing a shift schedule for a 4-member team roster with diverse shift patterns can be challenging.

      Each shift, A, B, C, and D, has a schedule for weekdays, weekends, and special arrangements on Wednesdays. Setting these parameters upfront allows you to assign team members to their shifts more efficiently.

      On Wednesdays, all four members are active but in different roles. It would be best if you had a flexible framework to handle this. One idea is to create a dynamic formula that adjusts shift assignments based on the day of the week and specific requirements. This might involve using conditional logic to distribute team members across day and night shifts, ensuring we have enough coverage while using resources effectively.

      Don’t forget to explore the resources in the article, including the practice workbook, to gain hands-on experience with building and refining your shift scheduler. Good luck.

      Lutfor Rahman Shimanto

  3. Dear Sir Lutfor Rahman Shimanto,

    I have also a query regarding on the sample, Instead of using D1,D2,D3,D4,OFF,A1,A2,A3,OFF, is it possible to use just like this only D,D,D,D,OFF,A,A,A,OFF..

    Thank you in advance.



    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 7, 2024 at 4:05 PM

      Hello ERAL

      Good to see you again. You wanted shifts like, Instead of using D1, D2, D3, D4, OFF, A1, A2, A3, OFF, is it possible to use just like this only D, D, D, D, OFF, A, A, A, OFF. Technically, it is impossible without VBA, but I have found a trick to reach your goal. I have used extra spaces in the Settings_Shift_Legend to make the shifts unique.


      Hopefully, I will like the idea. I have attached the solution workbook to help you understand better; good luck.

      Download Solution Workbook

      Lutfor Rahman Shimanto

  4. Hi Team,

    I need to create a shift for 5 Members Team. The shifts are

    Morning- Shift A
    Evening- Shift B
    Night- Shift C
    General- Shift D

    Shift Members are 1,2,3,4,5

    shift member 1 having week off on Friday & sat
    Shift member 2 having week off on Sun & Mon
    Shift member 3 having week off on Tue & Wed
    shift member 4 having week off on Thu & Fri
    Shift member 5 having week off on Sat & Sun

    *Shift member 5 do only General & when required Moring shift only.

    Can you please help to create this shift scheduler.


    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 7, 2024 at 5:06 PM

      Hello SHIVAM

      Thanks for your comment. To create a shift scheduler for a 5-member team with the specified shifts and off days, you can follow these steps:

      1. Create a table or spreadsheet with columns for the days of the week (Monday to Sunday) and rows for each team member.
      2. Assign each team member to a specific shift (Morning, Evening, Night, General) based on availability and preferences.
      3. Apply the off days for each member according to the given schedule (e.g., Member 1 off on Friday and Saturday).
      4. Ensure that Member 5 is assigned to the General shift and occasionally to the Morning shift when required.
      5. Repeat this process for each week or month, depending on the duration of your scheduling period.

      Here is an example of how you could structure the scheduler:

      Hopefully, the idea will help you; good luck.


      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF