In step 6 of last week’s post I suggested that you might want to just delete the formula from any cell that was marking a valid cell as “check”, so you could easily see when you had checked all of them. That’s fine whenever you’re doing a one-time check, but if you’re using the same sheet every month and just adding data to it each time, that could get tedious if you have to check the same things over and over.
There are a number of different ways to tell your spreadsheet not to mark valid missed dates, such as work holidays. And, as should come as no surprise by this point, all of them depend on an IF() statement.
Option 1: “OK” column
This one is probably the easiest one to conceptualize. Assume you have some data, and a column named “CheckStatus1”. For this option you’d add two more columns: “OK”, and “CheckStatus2″. In the Check2 column, enter the formula: =if(OK=””,CheckStatus1,””).
What that does is check to see whether there’s anything in the “OK” column. If there isn’t, then OK=””, so CheckStatus2=CheckStatus1. That means if CheckStatus1 is “check”, then CheckStatus2 is also “check”. Likewise, if CheckStatus1 is “”, then CheckStatus2 is also “”. However, if there is something in the “OK” column, CheckStatus2 will always be “”.
Then you search for the word “check” in the values of the CheckStatus2 column. When doing so remember that you can change the scope or direction of the search to search just the selected column or to search by columns instead of rows.
Once you encounter a “check” in the CheckStatus2 column, you would evaluate whether it’s missing data, or validly skipped data. If it’s valid, enter something in the “OK” column. It can be “y”, “Y”, “yes”, “ok”, “valid”, or basically anything else you want it to be. It doesn’t even have to be consistent – you could enter a brief explanation of why the exception is valid, such as “National holiday” or “Winter Break”. All that matters is that there’s something there, and the cell isn’t blank. However, to avoid visual confusion I recommend against using a space (” “).
Once you’ve done that for each entry you’ll never have to check those entries again, since they won’t be marked in CheckStatus2.
Option 2: Date limits by specified date
Another relatively simple, but more automated, approach is to not check anything older than a certain date, which you specify
To use this approach you need to already have a “Date” column and a “CheckStatus1” column. To these you’ll need to add a “CheckStatus2” column, and if you’re specifying a specific date you’ll need a cell named “DontCheckBefore”. As always these names can be anything you want, as long as they make sense to you.
Once you have those, the formula in CheckStatus2 would be:
This formula says that if the date of the entry is before your earliest to-check date, then don't mark it, but if it's the same as or more recent than your earliest to-check date, then mark it however CheckStatus1 is marked.
Option 3: Date limits by automatically calculated date
If you’re using an automatically calculated date, then you need to incorporate a formula to do that calculation. For example, if you download and check May’s stats sometime in June, then anything that’s before May can be skipped. So the formula here would be:
Option 4: External list of valid dates
You could also have a list of valid exemptions in another column, completely separate from the data. If that list were called “Exemptions” then the formula to check it could be:
That’s only one of the possible ways to do something like this. Another way would be:
I haven’t discussed isnumber() or match() yet, though, so if you’re not already familiar with them the countif() formula is just as functional, if a bit slower for large data sets.
Option 5: Internal IF() check
This one allows you to set specific days that it doesn’t have to check. I only recommend this if there are very few dates, or a predictable pattern, because it can get very complex quickly.
To start with, I’d recommend that you break the formula in CheckStatus1 into two columns: One to subtract the previous entry’s date from this entry’s date, and a second to check if that result is 1. For example, the first one could be named “CheckDifference” and the formula might be =B2-B1, if the dates were in column B. For daily statistics the second one, which I’ll leave as CheckStatus1, would be =if(CheckDifference>1,”check”,””).
Then, if you wanted to check to see if the skipped date is the fourth of July, the formula would be:
That checks if the date on the current entry is July 5th, and also if no more than one day was skipped between this entry and the previous one. After all, if you only checked to see if the current one was July 5th you’d never know if it was missing July 3rd in addition to July 4th.
If you wanted to check a holiday with a variable date, such as Labor Day (the first Monday in September), you could use a formula like:
This one has Day<8 because all you know is that it's in the first week, which means somewhere between 1 and 7. The other new part, mod(Date,7)=2, uses mod() to figure out what day of the week it is. In Excel, Open Office, and Google Docs a Sunday will always be 1, a Monday will always be 2, and so on up to Saturday, which would be 7 except for the fact that mod(7,7)=0, so a Saturday date will return 0. If you ever forget, don’t worry… as long as you know what day of the week it is today you can always do =mod(today(),7) and figure it out from there.
Now for a demonstration of why I don’t recommend this approach if you have a lot of different dates to check — if you combine those two formulas into one that will check both July 4 and Labor day would be:
As a side note, it is possible to simplify this formula a little, but not a lot. Rather like factoring 2 out of 24-18 to get 2*(12-9), you can factor out common conditions. This would yield: