Excel Timesheet with Different Rates for Shift Work

When creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.

In this tutorial we create a timesheet that uses different rates of pay for working weekends, and also night shifts.

Prefer a video tutorial? Skip to the video.

Calculating Hours Worked

In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.

Excel timesheet with different rates for shift work

Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.


If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.

(1+D5-B5)*24 Continue reading

Women’s World Cup 2015 Schedule – Excel Spreadsheet

The women’s World Cup is currently underway in Canada and at the time of writing have just finished the group stages of the competition.

This blog post provides a Women’s World Cup Schedule spreadsheet to automatically calculate the group stage rankings, and knockout stage schedule as the results are entered.

Download the Womens World Cup spreadsheet.

This spreadsheet is ready for use. All you need to do is enter the fixtures and results and it will take care of the rest.

What’s in the Workbook

The workbook is made up of 7 worksheets. Some of the worksheets are hidden as they are not needed when using the workbook. You can unhide these sheets by right clicking a sheet tab and selecting Unhide.

The following worksheets can be found in this World Cup schedule.

Teams – Stores the list of teams competing in the competition split into their groups.

Data – The number of points awarded for a win and draw in the groups stages.

Group Fixtures – The fixtures list and results during the group stages of the competition.

Calculations – Calculations for the group stage league table statistics such as number of wins, draw, losses goals for etc. It also calculates the teams league table rankings.

Third Place Rankings Table – Used to calculate the four best third placed teams during the group stages. It also calculates which teams they will face in round 16. This was quite complicated calculations and an explanation of how the schedule works can be seen below.

Group League Tables – The final group stage league tables. The VLOOKUP function is used to pull the information from the Calculations sheet, and use the ranking to list the teams in the correct order.

Knockout Stages – The fixtures and results for the knockout stage of the tournament. Formulas are used to determine which team progresses to the next round as results are entered.

How the Women’s World Cup Schedule Works

The first round is a group stage. Twenty four teams are divided into six groups of four teams. Each team plays one match against the other teams of the group. The group winners and runners-up progress along with the best four third-placed teams.

The ranking for each team in the group is determined by points, then by goal difference and then by goals scored.

Womens World Cup Group Stage Tables

The spreadsheet also contains a table to calculate the best four third-placed teams. They are ranked in their own table using the same criteria.

This is similar to other sports tournament schedules I have created in Excel. You can learn all the skills used for this in our online course.

Scheduling the Best Four Third-Placed Teams

What really made this tournament schedule exciting to create were the tournament regulations for who the best four third-placed teams play in the next round.

These four teams are to be matched with the winners of groups A, B, C and D. However the group winners they play is dependent on which group the best four teams are from.

The image below of the ‘Third Placed Ranking Table’ sheet shows the rank order of the best four third-placed teams and the group winners they are paired against. This schedule is from section 28 of the tournament regulations.

Scheduling the best four third-placed teams

Row 18 displays the top four third-placed teams. A formula was used in column F to then calculate from this the correct schedule to use. This formula made use of the IF, NOT, ISERROR and FIND functions.

This combination was then extracted to a different area of the sheet using INDEX and MATCH. The ‘Knockout Stage’ schedule sheet then pulled the relevant team name across.

5 Alternative Reasons to Use COUNTIF Function

The COUNTIF function is one of the most useful functions in Excel. Its job is to provide conditional counting. This is primarily used for analysing data and producing reports and dashboards.

In this blog post will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF examples we look at are;

  1. Prevent duplicates in a range.
  2. Uniquely rank items in a list.
  3. Count the unique entries in a list.
  4. Compare two lists.
  5. Identify names that occur 3 times or more.

Continue reading

Sum Negative Values Only in a List

You may have scenario on a spreadsheet where you only want to sum the negative values in a range.

Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.

Sum negative values only

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition (new to SUMIF? find out more).

The formula below will total the outgoings in the list by summing only the values less than 0.


The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.

The criteria specifies that only values less than 0 should be summed.

Watch the video

5 Advanced PivotTable Techniques

PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques.

  1. Grouping fields by month and year
  2. Calculating data as a percentage of the total
  3. Using Slicers
  4. Applying Conditional Formatting to PivotTable data
  5. Creating calculated fields

Continue reading

Add Drop Lines to a Line Graph in Excel

Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.

Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.

Line graph without drop lines

Continue reading

Export All Sheets to PDF – Excel VBA

This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.

By creating a macro this seemingly repetitive task can be performed at the click of a button. Continue reading

5 Reasons for Using Custom Fields in Microsoft Project

Custom Fields in Microsoft Project allow you to define your own fields (columns) for data that meets your organisation needs.

By creating your own custom fields you can display information on the project plan that is tailored to your requirements.

This tutorial looks at 5 reasons you may want to set up custom fields in Microsoft Project including creating drop down lists and using formulas in fields.

  1. Create a drop down list
  2. Display multiple fields next to task bars in Gantt Chart
  3. Sum the total duration of tasks
  4. Change colour of task bars dependent upon conditions
  5. Using graphical indicators in custom fields

Continue reading

Embed Fonts in PowerPoint File

If you are sharing a PowerPoint presentation with others, you may decide to embed fonts in the PowerPoint file. This will ensure that others can use those fonts, even if they are not installed on their machine.

If you have used a custom font that another user does not have, it will be replaced by the default font on their machine. The presentation will then have an inconsistent look to it.

Be aware that embedding fonts in a PowerPoint file will increase its file size. However this is typically a small price to pay for a consistent and reliable presentation. Continue reading

Add a Hyperlink to a Userform – Excel VBA

If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.

You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.

Adding a Hyperlink to a Userform

In this example we will add a hyperlink so users may email an enquiries department from the userform. Continue reading