I’m trying to set up a call log in Excel 2010, and I need one column to be conditionally formatted to show either Yes or No: Yes if A4 date is equal to E4, No if A4 is less than E4. We want to know if we’ve recontacted customers within a 24-hour period. How do I set that up? – Marshall
Marshall, you can’t use conditional formatting to display specific text. You’ll need a formula to do that. Yes if A4 date is equal to E4 suggests that ideally, you’d like the follow-up call to fall on the same day as the inquiry. In this case, a simple IF() will do the trick.
There’s a bit of a conflict between the two rules that you state:
- Yes, if A4 is equal to E4, No if A4 is less than E4 implies the same date.
- We want to know if we’ve recontacted customers within a 24-hour period implies that the follow-up call could occur the next business day.
You can’t easily consider both of the simple expressions in your first rule, but the less than comparison give you a bit of an edge. I used a less than comparison in column F and an equals comparison in column G:
In the first two records, they both return the appropriate response. The problem is the third record. If by chance, through data entry error most likely, you enter an inquiry date that’s later than the follow-up date, the first formula won’t catch the error, but the equals comparison will. Keep in mind that neither expression cares if the follow-up date occurs within the next 24 hours. It only cares if the follow-up occurred on the same day, which may or may not be what you meant.