How to Create a Basic Excel Shift Rota

May 6, 2015

How to Create a Basic Excel Shift Rota

Are paper timesheets giving you a headache? If so, then why not create your own basic excel shift schedule template?

This is probably the most simple excel shift rota to build. As such, it lacks a lot of sophisticated functionality and requires manual data entry. If you want to make things really easy, we suggest using RotaCloud – their software integrates perfectly with People™’s HR system, and building shift rotas is a piece of cake.

For now though, here is the basic way to create an excel shift schedule template (that also works for most spreadsheet programs):

  1. You will need 22 columns in total.
  2. Label the first column ‘Name’.
  3. Label the next three columns: Monday IN. Monday OUT. Monday HOURS.
  4. Repeat step 3 for each day of the week.

From here, you can simply use this as your template and manually enter the data yourself. To add a bit of automation, however, you can use a FORMULA to ask the HOURS cell to calculate the total hours worked for each person on each day. To do this, you’ll need to:

  1. Select the HOURS cell.
  2. Type the following: =SUM(
  3. Click the IN cell
  4. Type the following: –
  5. Click the OUT cell
  6. Type the following: )

The end result will probably look something like this: =SUM(A2-A3)

You can apply this formula for each person in the NAME column, and for each day of the week.

What Happens Next?

Providing you have done everything right, you should be able to simply add each employee’s name to the NAME column, then add their start and finish time for each day of the week – the hours for each day will automatically pop up.

Of course, we highly recommend avoiding spreadsheets altogether and trying an automated HR solution, such as People™’s HR system or RotaCloud’s shift software… Watch this 4-minute video to discover more ways to make HR easier


Stay ahead with HR

Get free HR insights, expert tips and exclusive interviews, and start making more impact at work

Please enter a valid email address

See People HR in action

View our short video demo

Get started absolutely free

No credit card required

Talk with an expert to learn how People HR could help your organisation