Automated Work Schedule in Excel

Still typing out employee schedules by hand or dragging cells like it’s 2005? Let’s fix that with an automated work schedule in Excel.

This guide will walk you through how to build a dynamic, colour-coded employee schedule that:

• Automatically generates the dates for any month
• Calculates finish times from standard hours and start times
• Highlights days off, holidays, and sick days with drop-downs and colours
• Summarizes days worked, sick, and on holiday
• Looks good and saves hours each month

Of course, you can customize it to suit your unique requirements once you have the foundation.

Automated Work Schedule Video

Get the Practice File

Build a Smarter Work Schedule in Excel (That Updates Automatically)

Watch the video above for step-by-step instructions on how to build an automated work schedule, or follow the written instructions below.

Step 1: Generate the Dates for the Month

=SEQUENCE(1,EOMONTH(B3,0)-B3+1,B3)

3. Format the date cells:

• Select the cells, press CTRL + 1 to open the Format Cells dialog.
• Go to the Number tab → Custom.
• Enter this custom format:
  d CTRL+J ddd
  (Press CTRL+J between the d and ddd to wrap the day name on a new line.)
• Then, go to the Alignment tab:
  • Check Wrap Text
  • Set Horizontal Alignment to Center

• Increase row height to fit the text.

It should look like this:

Step 2: Set Up the Employee Table

1. In column A, list your employees:
Example:

• A6: Alice
• A9: Ben
• A12: Chloe

Each employee needs 3 rows:

• Row 1: Work status
• Row 2: Start time
• Row 3: Finish time

2. In column B, input each employee’s standard work hours:

• B6: 8 (Alice)
• B9: 7 (Ben)
• B12: 6 (Chloe)

It should look something like this:

Step 3: Add Drop-Downs for Work Status

Working,Day Off,Sick,Holiday

(Add/remove options from the list to suit your requirements.)

Mine looks like this now:

Step 4: Colour-Code Work Statuses

• “Working” → blue fill
• “Day Off” → grey fill
• “Sick” → orange fill
• “Holiday” → green fill

It’s coming along nicely:

Step 5: Automatically Calculate Finish Times

=IF(AND(C6="Working",C7<>""), C7+TIME($B6+1,0,0), "")

This checks if the employee is working and has a start time, then adds standard hours +1 hour for lunch. Modify the +1 to suit your break duration.

3. Copy this formula across the row and down for other employees.

Step 6: Freeze Panes for Easy Scrolling

Select cell C6 > go to View > Freeze Panes > Freeze Panes

Now, names, standard hours and dates in row 5 stay visible while scrolling.ces (like Allocation), you can even skip the extra brackets after the @ sign:

Step 7: Add Visual Dividers Between Employees

=$B6="Finish"

Step 8: Create a Dynamic Heading

1. In cell A1, enter:

="Work Schedule - " & TEXT(B3,"mmmm")

Step 9: Add Summary Figures (Optional but Useful)

1. Use COUNTIF to calculate how many days were worked:

• In E3: label = Days Worked
• In F3:

=COUNTIF(C6:AG30,"Working")

Repeat for “Sick” and “Holiday” if you want more summaries.

2. Add icons using Insert > Icons, search for:

• Computer (working)
• Doctor (sick)
• Sun (holiday)

Resize and colour to match your theme.

Step 10: Customize and Expand

You can change the standard hours, start times, or status drop-downs to suit your team.

To add new employees, just insert a new 3-row block and copy formatting and formulas.

Next Steps

If you enjoy this kind of logic-based setup and want to feel more confident using formulas like these, I teach everything from Excel formulas to dashboards and Power Query in my online Excel courses here.

These are the exact tools I rely on when building templates like this, and they’ll help you go from basic spreadsheets to time-saving systems.