# Excel Roster Hours and Overtime

Contents

- Excel Roster Hours and Overtime
- Download the Template for Excel Roster Hours and Overtime
- Overview for this project
- Watch this video for an overview of what these rosters will do: Video 1
- This application can be further developed as shown in this video: Video 2
- Excel Roster Hours and Overtime: Part 2 Shift codes
- Excel Roster Hours and Overtime: Part 3 The roster sheet
- Excel Roster Hours and Overtime: Part 4 Conditional Formatting
- Add formulas to the Interface sheet

There has been a great amount of interest in the formula based Excel roster that we published late 2012. Literally thousands of site users have worked through that project to develop successful rosters that they use for a great variety of applications.

I have had scores of inquiries asking how to calculate hours for each individual and each day within a roster period. In this roster hours tutorial I’m going to show you how you can accomplish this using formulas.

This is a kaleidoscope roster with dependent lists and has the added feature of a very nice interface sheet that summarizes shift, hours and role distribution. It even shows a cost approximate analysis.

## Download the Template for Excel Roster Hours and Overtime

### Template Hours Roster

I would recommend that you download the template because it will make it easier for you to complete this tutorial. When using the formulas that I'm providing below then this template will fit them like a glove. So save time and heartache – use the template. After you have completed the tutorial then set up your own template and modified the tutorial to suit your own needs.

### Here is a quick view of the 3 worksheets in this Excel Roster Hours and Over time tutorial.

I have used only one roster sheet in this project and have linked the relevant distribution totals to the interface sheet. It is possible to use multiple rosters and link them to the interface sheet if you so desire. For a large analysis of roster data I would strongly recommend using a mirror sheet as is shown in our second video tutorial below.

### Interface Sheet

This sheet contains all of the totals for our hours/shifts/role and the cost distributions. If you are using the templates I have left the three charts in the templates so that when you start to add the totals on to this sheet the charge should function automatically.

### Roster Sheet

The image of the roster sheet that you see below is with the look up columns hidden. When you download the templates these columns will be unhidden few and I would strongly urge that after every think is working as you want that you hide the columns and it then also protect the worksheet. This will stop staff from accidentally deleting your formulas and your beautiful roster.

### The List Sheet

I have used times in here for our roster except for the Other shift types where I have used the text codes. The whole roster can function with just text codes if that is what you wish. You will also notice a feature here that when we put in the text code in the format that I have shown that formulas will automatically pull it apart and assign the appropriate hours. This is not necessary in order to have the roster function but I thought it makes things easy and safe is a bit of calculation. The end of the day it is up to you whether you wish to use the set up or not.

## Overview for this project

This basic Excel roster project will work in Microsoft Excel 2010 and 2013. There is no VBA code in this roster system it is created completely with formulas. Please take the time to watch the video below before commencing the project. I would strongly urge you to use the templates and follow the tutorial as it is shown here before you change it to your local needs.

## Watch this video for an overview of what these rosters will do:

Video 1

This video shows the features of our roster and I strongly urge that you watch it before proceeding. It could save you a lot of time. All of the videos on my website are non-scripted or ad hoc so if I have missed something in the description I do apologise.

## This application can be further developed as shown in this video:

Video 2

The video below is an adaptation of this project and will be a future tutorial. This is closer to what I would use if I was developing a hours roster system for a small hospital. it will allow for massive calculations to mirror your roster sheets. Although not shown in this video a summary sheet can be provided that a gain links to the data on the mirror sheet.

## Excel Roster Hours and Overtime: Part 2 Shift codes

### The Shift Codes: Roster Hours and Overtime

Let's get started by developing the sheet that holds all of the codes that we will use in our roster system. The way that we set up this sheet is very important to the functionality of our roster system. Having accurate codes and the times that they represent is the foundation for any good roster. Remember we will be using VLookup formulas to access this information from a table. That being the case your need to set this table up as I have shown here..

### Add all codes for the shifts

### Lists sheet

**2 Dynamic named ranges**

* Note:* It is imperative that you check that your dynamic named ranges and in fact all named ranges are working before you add them to your formulas. You will notice in the illustrations below that I am in the Name Manager and the named ranges are working and highlighted by a moving dotted green line.

Here are links to 2 dynamic named range tutorials

#### 6 Types

#### Basic dynamic named range

**AllCodes**

=OFFSET(Lists!$D$7,,,COUNTA(Lists!$D$7:$E$1000),5)

**Cat_Full**

=OFFSET(Lists!$E$7,,,COUNTA(Lists!$E$7:$E$1000))

**4 Static Named ranges**

1. **Category**

=Lists!$J$7:$J$10

2. **Cascade**

=Lists!$D$6

3. *InterfaceSheet*

=Interface!$A$9

4. **RosterHoursSheet**

=RosterHours!$A$1

Named ranges numbers three and four are to be assigned as hyperlinks to the buttons on the application. They will be used in order to you to navigate around between your sheets without going to the tabs at the bottom. In fact I would generally hide the tabs in the finished application and only allow navigation via hyperlinks. Why is this? Well! It stops users from deleting your sheets.

### Add formulas if you wish to calculate the time

Here are formulas if you want to auto extract the times. This is not necessary but I like it because it saves me a lot of calculations manually. If you adjust any shifts than those changes will automatically be reflected in the hours. For these formulas below to work you would have to use the type of code that I have shown and add the formulas to the cells that I have referenced.

**Sheet “Lists” Cell F7**

=LEFT(D7,2)&":"&MID(D7,3,2)

**Sheet “Lists” Cell G7**

=MID(D7,6,2)&":"&RIGHT(D7,2)

**Sheet “Lists” Cell H7**

=IF(D7>"",(G7+(F7>G7)-F7)*24,"")

## Excel Roster Hours and Overtime: Part 3 The roster sheet

### The Roster Sheet

We will now move over to the roster sheet and construct all of its necessary parameters. All of the formulas are listed below along with the data validation to make the creation of this roster sheet super easy. Okay maybe not super easy but at least easier.

### Header block for dates

The header block contains all the dates and also the data validation for our categories of the shifts. When we add our starting date to cell E6 we want or all of the dates to populate across our roster automatically.

### Formulas for the dates

*Click the image below to enlarge it*

*Cell I9*

=E8

*Cell L9*

=I9+1 then O9 =L9+1 and add these formula to the end of the date range

**Cell I8**

=L9

*Cell L8*

=I8+1 then O8 =L8+1 and add these formula to the end of the date range

*Cell I6*

=I9

*Cell U6*

=AA9

*Cell AD6*

=AD9

*Cell AP6*

=AV9

*Now add the start date to cell E6 and everything should populate automatically
*

### Data Validation

1. * Select cell E8* on the Roster sheet

2. On the

*choose*

**Ribbon**

**Data / Data Validation**3. In Settings select

*from*

**List**

**Allow**4. Click in the

*box and hit the*

**source***key and choose*

**F3**

**Categories**5. Click

**OK**

### Now for the dependent validation

1. * Select the area* where you want to add the dependent validation. (

*)*

**Columns I,L,O,R etc between row 11 and 38**2. On the

*choose*

**Ribbon**

**Data / Data Validation**3. In Settings select

*from*

**List**

**Allow**4. Click in the

*box and*

**source***in the formula below.*

**paste**

**=OFFSET(Cascade,MATCH($E$8,Cat_Full,0),0,COUNTIF(Cat_Full,$E$8),-1)**This formula is referring to 2 of the named ranges that we created earlier. That is why it is critical that you make sure that the named ranges are working before you proceed.

* Note: *You can now check to see if all is working OK. Do not move forward with the project until all of this is working satisfactorily.

*Here is a link to a tutorial on Cascading Data Validation*

### Cascading Data Validation

*Lookup formulas*

*Lookup formulas*

These formula are added to the hidden columns. These formulas as you can see are looking up our table with all of our shift codes. Firstly they look up column 2 of the table to get the Category and then we look up column 5 in order to get the number of hours. I have used the IFERROR function in order to remove any errors. If you are using an earlier version of Microsoft Excel then you would need to replace this with the IF and ISNA functions respectively.

*Cell J11*

=IFERROR(VLOOKUP(I11,AllCodes,2,0),"")

*Cell K11*

=IFERROR(VLOOKUP(I11,AllCodes,5,0),"")

You are going to need to add these formulas to all of the hidden cells on your roster sheet. You could do this by using copy and then paste formulas or by drag-and-drop.

### Formulas to calculate hours

These are the hour calculations on the right hand side of the sheet.

*Cell AY11*

=IF(G11="","",SUM(I11:AX11))

Notice the 8 and 10 in the formula below. This is calculating and * 8 hour* shift with a maximum

*per roster.*

**10 shifts***Cell AZ11*

=IF(G11="","",($G11*8)*10)

*Cell BA11*

=IF(AZ11="","",AY11-AZ11)

With the formula below we are filtering out only over time worked.

*Cell BB11*

=IF(AND(AY11>AZ11,AZ11>0),AY11-AZ11,"")

*Click the image below to enlarge it*

### Totals for each day at the bottom of our roster sheet

*Click the image below to enlarge it*

Note: These formulas will need to be copied to all of the cells that will not be hidden at the bottom of the worksheet.

**Add the formulas to Count the number of shifts**

Cell I39

=COUNTIF(J$11:J$38,$D$40)

Cell I40

=COUNTIF(J$11:J$38,$D$42)

Cell I41

=COUNTIF(J$11:J$38,$D$44)

Cell I42

=COUNTIF(J$11:J$38,$D$46)

**Add the formulas to total hours per shift**

Just a couple of points about the DSum function. It works very much like an advanced filter and will enable you to use multiple criteria in order to filter the data in columns. I have used here so that you can at least experience how it works. Yes! The calculation could be done with the SUMIF function. but if you wanted to use multiple criteria than the DSUM function is absolutely the way to go. It requires a header and then a criteria underneath the header that is referenced. Exactly the same as an advanced filter. If you have done other tutorials of mine you will know that I am mad keen on the advance filter and the awesome power that it can give you an filtering data.

*Cell I44*

=DSUM(J10:K38,2,$D$39:$D$40)

*Cell I45*

=DSUM(J10:K38,2,$D$41:$D$42)

*Cell I46*

=DSUM(J10:K38,2,$D$43:$D$44)

*Cell I47*

=DSUM(J10:K38,2,$D$45:$D$46)

*Add the formulas to total hours by role per day*

**Cell I49**

=SUMIF($C$11:$C$38,$C$40,K$11:K$38)

**Cell I50**

=SUMIF($C$11:$C$38,$C$42,K$11:K$38)

**Cell I51**

=SUMIF($C$11:$C$38,$C$44,K$11:K$38)

*Add formulas to sum these values in column H as per the illustration above*

Add these formulas to * G39 to G52 *they will give you the totals for all of our days within the roster.

=SUM(I39:AV39)

=SUM(I40:AV40)

=SUM(I41:AV41)

=SUM(I42:AV42)

=SUM(G39:H42)

=SUM(I44:AV44)

=SUM(I45:AV45)

=SUM(I46:AV46)

=SUM(I47:AV47)

=SUM(G44:H47)

=SUM(I49:AV49)

=SUM(I50:AV50)

=SUM(I51:AV51)

=SUM(G49:H51)

* *

*Total Hours*

**Cell AY6**

=G48&" Hrs"

*Target Hours*

While I have called this target hours what it simply refers to is that this would be the number of hours that staff would work if everybody completed their shift allocations. It is not important to the function of the roster that I just thought I'd put it in to show variance.

**Cell AY7**

=SUM(AZ11:AZ38) &" Hrs"

In the next section of this series I'm going to show you how to add the conditional formatting that is going to turn this roster into a * kaleidoscope* roster.

## Excel Roster Hours and Overtime: Part 4 Conditional Formatting

### Conditional Formatting for the Hours Roster Sheet

While it is time to put your sunglasses on because we're going to be adding some conditional formatting that is going to turn our roster into a spectacular kaleidoscope of colours. Kaleidoscope rostering enables you to see by colours the type of shift that you have. Many find this very effective in quickly recognising shift details.

to do this will be adding a formula are into our conditional formatting. Look closely at the illustrations below and follow the instructions and all the lights should switch on.

*Click the image below to enlarge it*

As previously mentioned conditional formatting will create the kaleidoscope effect for our roster. When we enter an Early shift we want to sell to change to green and then when we had a Late shift to a blue for Night shift we will turn the colour red and for our Other shifts a mauve colour. You can change these colours to whatever floats your boat. But a word of caution hard bright colours are not easy to look at over time. Pastels would be the preference.

1. * Select* the ranges

*and on the*

**I11 to AX38***tab under*

**Home***choose*

**Styles**

**Conditional Formatting**2.

**New Rule**3.

**Use a formula to determine which cells to format**4.

**Format values where this formula is true**5. Add this formula

**=VLOOKUP(I11,AllCodes,2,0)="Early"**6. Click the

*button and*

**Format***accordingly and click*

**choose the formatting**

**OK**7. Now

*exactly the same with different formatting using the text*

**add 3 new rules***and then*

**“Late”***and*

**“Night”***as the criteria. Assigned the appropriate colours.*

**"Other"**

### Conditional formatting for staff hour variance

This conditional formatting is super easy. We will be simply assigning data bars.

*Click the image below to enlarge it*

1.* Select BA11:BA38 *and on the

*tab under*

**Home***choose*

**Styles**

**Conditional Formatting**2.

**Data Bars / Gradient fill**

## Add formulas to the Interface sheet

I'm not going to go into too much detail about how the interface sheet should be set up. This is largely a matter for your personal needs and taste. If you're running a single roster than you could set up as I've shown you here in this tutorial. If you are running multiple rosters then you would need to link those rosters to your interface sheet and sum of them on the sheet.

*Click the image below to enlarge it*

Well that's about it for this tutorial. I hope you have found that it is useful. Don't forget to protect each worksheet. If you have use the templates provided then all of the necessary cells will be unlocked for you and you can just simply protect the sheet with or without a password to stop users from damaging your precious work.

Hide the headers and the formula bar is on each sheet and then protect the sheets before you send your work out into the world.

Thank you very much for joining me and I hope you have a great day.