1. Checking a payroll register
OK, we're comfortable now that the daily detail is correct. Let's move on to the weekly detail.
2. Checking for OT
This is all of the detail for the weekly pay that you are processing. The employees must work 40 hours during the pay period before they are eligible for overtime. You are concerned that some employees are coded for overtime pay even though they have not reached 40 hours. So let's check for that.
3. Custom formula AND
We can use the AND formula that we touched on earlier in this course. Remember that the custom formula in conditional formatting is already like an IF statement so this is like saying IF C2 is less than 40 and E2 is greater than zero, format the cell. Pretty easy once you understand what the custom formula box is really asking you. It's just waiting to highlight your range when what you give it is TRUE.
Keep in mind that there are also greater than and less than functions available in the menus, but we aren't using them here because you can't combine them like we did with the AND formula.
4. OT fixed
We've cleaned up the data by moving the overtime to regular time for the employees indicated with the conditional formatting. Next we're going to apply a new rule. Also, we'll delete the rule for the overtime hours to keep things clean.
5. Range of pay rates
We can illustrate the range of pay rates by applying a conditional formatting rule to the regular rate data using a color scale.
Let's leave the default formatting option, as it does a decent job of showing what we want to show. This is being used as a softer kind of reporting meaning that you don't need to take any action on this data, but the formatting helps to show the reader who is getting paid what. You can leave this formatting on when you pass the report up the chain.
6. No production employees
You think that you are done checking the weekly pay detail when you learn that someone down the hall from you has been paying the production employees for the last 25 years and you shouldn't have any of them in your payroll report.
Instead of just removing them, you think it would be more helpful if you left the production employee on the list and showed that they were inadvertently included and should be removed going forward.
How do you show something like this? Conditional formatting, of course!
7. Text contains
Select the range with the Department names and use the pre-built Text contains option. Simply type in Production; you don't need the quotes in this box.
8. Remove production
You want to convey the message that this employee should be removed, so let's include a strikethrough. You can leave the default green background because it helps to bring attention to the fact that there's action needed on this employee.
9. Payroll report is done!
And there you have it! You have created a report that is free of overtime errors, shows the range of pay rates, and points out an employee that should be removed. All without leaving the comfort of your keyboard.