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.

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.

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.

(D5-B5)*24

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

Now in this timesheet it is not quite that straight forward. We need to test first what shift they are doing and whether it was worked overnight or not, so that the correct formula can be run.

The formula below is entered into column F to calculate hours worked on the day shift.

=IF(E5="Day",(D5-B5)*24,0)

In column G, the formula below was used to calculate hours worked on the night shift. The IF function first tests if it was a night shift, then test to see if it was worked overnight (A5<C5), and runs the correct calculation as a result.

=IF(E5="Night",IF(A5<C5,(1+D5-B5)*24,(D5-B5)*24),0)

[Read more…] about Excel Timesheet with Different Rates for Shift Work

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 aggregates for reports and dashboards. However, there are many cool COUNTIF function examples in Excel

In this blog post, we will look into 5 more unorthodox but useful scenarios for the COUNTIF function to be used. The 5 COUNTIF function 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.

[Read more…] about 5 Cool COUNTIF Function Examples

You may have scenario on a spreadsheet when you want to sum negative values only 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.

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition.

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

=SUMIF(B2:B12,"<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

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

1. Summing a range that contains errors
2. Creating a cumulative total
3. A consolidated sum from multiple sheets
4. Summing Every Nth Value in a Range

[Read more…] about Advanced Techniques for Summing Values