An Excel Workaround for Conditional Formatting That Shouldn’t Be So Hard
Dear Microsoft,
I use the conditional formatting function in Excel for pretty much only two reasons. To flag invalid data entry and to identify outliers. Why is that so damn hard?
I’d like to be able to tell you “if the first digit of the military time is above 2 or the 3rd digit is above 5, flag it as invalid,” but I cannot. Maybe this is possible, but it’s really hard to figure out. So here’s how I did it:
First, take the cell in question, then tab over into the margin (or any other blank space, in my case, I needed to do this for two cells on every row). Next, set up a nested formula that checks for the conditions you want and returns a boolean. In my case that formula was:
=IF(cell<>”“,AND(Value(Left(cell,1))<3,Value(Left(cell,2))<24,Value(Right(Left(cell,3),1))<6,Len(cell)=4),”“)
In other words, if the cell in question (“cell”) is empty, return nothing, otherwise return true if the first digit is less than three, the first two digits are less than 24, the 3rd digit is less than 6, and the length of the string is 4. That is, only proper military times can be entered.
Next, having coded all the conditions into that cell, I select the conditional formatting menu on the target cell and set it to have a red background (my preferred flag) if the value of the other cell is “FALSE.” I would prefer to use data validation to reject this entry, but the red flag has to do, because I can’t use validation to check the value of a child cell, this should also be possible.
This workaround is annoying. Please make this easier to do, either in the data validation menu (the “custom” criteria are not intuitive, and I am fairly certain specifying the value range of digits is not possible) or in the conditional formatting menu (again, the rules are not intuitive with respect to this goal).
Thank You,
—Dave
-
30daysatatime posted this
