Comparing dates

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:

=IF(A2<E2,"No","Yes")

=IF(E2=A2,"Yes","No")

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s