1. Working with payroll dates
You're processing payroll for the first time and you're nervous it won't go well. No worries! You can use your newfound conditional formatting knowledge to power through it! In this chapter, we will dip into everything that you learned in this course and throw in some new functions to help you along the way.
2. Employee pay detail
We are going to start at the lowest level of payroll, which is individual employee pay data. The detail that you see is for one week of pay. This information has been input by hand, so you need to check it very carefully. Along the left-hand side are the pay dates, which are often typed incorrectly.
This is a common situation in data that doesn't come out of a computer system. Conditional formatting of dates won't work if the dates that are formatted aren't valid because the spreadsheet doesn't know that they are dates.
3. ISDATE Function
A built-in method for checking dates is the ISDATE function. This function returns TRUE if the value is a valid date and FALSE if it is not. Sounds perfect for conditional formatting, right?
You may think that you can tell by looking, but that isn't always the case. Dates can be broken if they have spaces, are entered as text, or if they are days that don't exist, like the 32nd of March.
4. Checking for valid dates
Since the ISDATE function gives a nice TRUE FALSE result, we can throw the function right in the custom formula input box without any other monkey business.
We'll use the range A2 to A10, which is where our dates lie.
As you can see, the formatting has pointed out that the value in cell A7 is not really a date since it has a space in it.
5. Menu option
Now that we know which date is bad, I've taken the space out of the date to fix it. We no longer need this conditional formatting rule. Instead of leaving it there, let's take it off of the Sheet by deleting the rule. Select the range that you highlighted, then go to Format, Conditional Formatting.
6. Remove the rule
You can find the option to delete the rule when you hover over it with your mouse. Let's just remove this one to keep things from getting cluttered.
7. Check for duplicates
Another common error in payroll data is the accidental, or at least hopefully accidental, duplication of data. Using the technique that we learned in the last chapter, we are going to check the date field for duplicates.
You find two entries for June 10th, but you know the employees don't work double shifts, so you do a little checking and find out that the first line isn't supposed to be there.
We'll delete that row and also delete that conditional formatting rule. Let's do one more check.
8. Date range
The company pays every week, so you should only be looking at pay date for 7 days, from Sunday through the following Saturday. It would seem that we could use the built in date option that we covered earlier, but those are only for relative dates. These dates are fixed, but there is another built-in option that we can use which is called Is between.
Just type the two date values that you want in the boxes, without quotes, and the formatting will be applied. You'll see that June 15th is outside of the range of dates that we need. Delete June 15 and we're ready to move on to the next step.
9. Ready to go!
Now you're ready to look at a higher level of payroll data which is the pay register for all employees in the period.